MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Comparing/Matching lists


Posted by Squirrel on January 24, 2002 4:31 PM

In my job I have been ask to find a method of comparing two lists of 17 digit numbers. From this comparison, I need to decipher the duplicates. Is there a method for doing this other than using a concatenation formula with an additional column for tagging the duplicates?


Posted by bob umlas on January 24, 2002 5:43 PM

Assume your lists are range names, named "Mine" and "Master".
With F1 blank, enter this is F2 by pressing ctrl/shift/enter:
=MATCH(1,ISNA(MATCH(Mine,Master,0))*(F1<ROW($1:$100)),0)
In G2, enter:
=Index(Mine,F2)
Now fill down as far as you want -- Column G will show the duplicates.