VBA for finding duplicate values in 4 columns? #Stumped

Hi Excel Masters,

I'm trying to find some code that allows for duplicate values to be highlighted across a hundred thousand rows of data.

The data is filtered down on four companies (A-D) that each contain the same group of states that I'm filtering down on. Attached is an example of what I'm looking for where Excel will highlight the information that is identical in columns C and D. The code will then go through each of the entries in Column B's filter and highlight the duplicates in C and D.

Anyone able to help with this? Thanks!

I'm not sure I fully understand your sample attachment. Why aren't the NY 42nd St. entries highlighted, they appear multiple times? My understanding was that if State/Road/W.A. all match on at least two entries, then those entries should be highlighted, which can be done with conditional formatting using the following CF formula:

=COUNTIFS($B$2:$B$2000,$B2,$C$2:$C$2000,$C2,$D$2:$D$2000,$D2)>1

Change the 2000s to match whatever your last row of data will be (or beyond it). Take a look at the attachment to see if I'm close. If not, can you clarify what should and shouldn't be in?

Originally Posted by CAntosh

I'm not sure I fully understand your sample attachment. Why aren't the NY 42nd St. entries highlighted, they appear multiple times? My understanding was that if State/Road/W.A. all match on at least two entries, then those entries should be highlighted, which can be done with conditional formatting using the following CF formula:

=COUNTIFS($B$2:$B$2000,$B2,$C$2:$C$2000,$C2,$D$2:$D$2000,$D2)>1

Change the 2000s to match whatever your last row of data will be (or beyond it). Take a look at the attachment to see if I'm close. If not, can you clarify what should and shouldn't be in?

Hi CAntosh,

Sorry for not clarifying that part. In order for a row to be highlighted in Column C and D, it has to match on both columns. So, '42 Street' isn't highlighted because it's a 'no' for Company D in column D. Similarly, 'Belt Parkway' also isn't highlighted because it only exists in Company B. The rows need to have a 1:1 match in column C and D for it to be highlighted. Also, some rows may appear multiple times for Company A, but only once for Company B, and never for Company C and D. Hence, it would not be highlighted at all.

Thanks for your help! I really do appreciate it!

Thanks for clarifying! I think I get it now (fingers crossed).

Same general plan as before, but try the following as your conditional formatting formula:

=COUNTIFS($B$2:$B$47,$B2,$C$2:$C$47,$C2,$D$2:$D$47,$D2)=SUM(--(FREQUENCY(IF($B$2:$B$47=$B2,MATCH($A$2:$A$47,$A$2:$A$47,0)),ROW($A$2:$A$47)-ROW($A2)+1)>0))

Test it out in the attachment:

Thanks for the help, CAntosh!