Matching records based on multiple columns

  • Thread starter Thread starter looking for answers
  • Start date Start date
L

looking for answers

I need to compare records from two different sets of data.
Set 1 is a master list and Set 2 is a sublist of the master.
The comparison involves three columns from each set; Tag#, Location, S/N.
I want to be able to match records from Set 2 to Set 1, ending with an
additional column that shows if there is a match (true/false).

What is the most efficient way of completing this task? Thanks
 
concatenate a column that contains the 3 required fields on both tabs, then
do a vlookup.
 
I would add two helper columns to each worksheet.

Say new columns A and B in both sheets.

In column A, I'd concatenate the 3 fields that make up the keys:

=c2&"---"&d2&"---"&e2
(the "---" is a unique string to make sure the concatenated strings don't match
just by chance.)

Then drag down as far as I needed.

Then I could use a formula in column B:

=isnumber(match(a2,sheet2!a:a,0))
drag down

And then filter by this column.

Doing this on both sheets would show where were missing (or there) on the
opposite sheet.
 
Back
Top