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.

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.

• google-sheets
• duplicates

1 Answer 1

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:

• COUNTIF()