What I am trying to do is to check for duplicates within my column H (consist of URLs) and column J (consist of URLs). I am using conditional formatting but somehow it only works if I added a duplicate URL within my column H but if I added a duplicate URL from my column J it is not working. here's the formula and range I use:
Apply to range: H1:J1391
Custom formula:
=OR(COUNTIF(H:H, H1)>1, COUNTIF(J:J, H1)>1)
For context:
Column J - imported URLs from different google sheet ( formula used: =importrange), the column I imported are constantly getting updated
Column H - consist of URls as well, this is where I add URls and where I want to check for duplication within the column and against column J.
This is for work btw, what I do is add URLs in my column H and these URLs are getting added to a different google sheet (where I imported my URLs from column H). I just want to make my life easier that's why I want to create this duplicate detector.
I expect that when I add a duplicate URL from column J to column H it will highlight but somehow my formula only works if I add a duplicate URL from column H.
You almost got it. How about adjusting the last condition of Countif to >0 instead of >1
Try this Google Sheets formula:
=OR(COUNTIF(H$1:H, H1)>1, COUNTIF(J$1:J, H1)>0)
I have used the absolute reference for each range and adjusted the condition. This formula checks if the value appears in both column H and column J. If yes, it will set the value to TRUE.
Reference: