Compare List A to List B and Return Duplicates to C

**nexttech** · 12-02-2009, 10:45 PM

Hopefully someone out there can help. I need help creating a macro which compares 2 columns of data and all duplicates written to a third column. Here are the steps I believe need to happen, but I'm having trouble translating it to VBA code...

Write a for loop with “If Statements”
Compare A to B to see if it is equal if it is write it to C and move on to the next A
If A is not equal to B move to the next B and repeat (so each A is being checked against every B until a match is found or all B's have been checked)
Once a match is found or all B's have been checked repeat the process with the next A

I know it will take a clever solution, so any help is most appreciated! Thanks in advance for your time.

**shg** · 12-02-2009, 11:08 PM

Why a macro?

In C1 and copy down =IF(ISNUMBER(MATCH(A1,B:B, 0)), A1, "")

**nexttech** · 12-02-2009, 11:16 PM

Thanks for the reply, but its more complicated than that. I cant just compare A with the B right next to it. I need to compare every A with every B, not just the values in one row.