Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching records based on multiple columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching records based on multiple columns
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. looking for answers wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum based upon matching data in different columns | Excel Worksheet Functions | |||
Matching records in diff columns | Excel Discussion (Misc queries) | |||
Matching multiple columns | Excel Worksheet Functions | |||
return multiple records matching multiple criteria | Excel Worksheet Functions | |||
Extract multiple records matching criteria from list | Excel Worksheet Functions |