Another excel question - comparing two tables

Associate
Joined
11 Dec 2009
Posts
1,603
Hi all,

I have two tables, these are as follows:

Customer Number | PO Number
1 | 1123
1 | 1129
2 | 1126
3 | 1124
4 | 1164
4 | 1243

Customer Number | PO Number
1 | 1123
2 | 1129
3 | 1124
4 | 1164
4 | 1243

I want to compare the two tables to see which customer and related PO are in each (whilst considering that a customer can have multiple PO numbers.

The columns I envisage being in the comparison are:

Customer Number | PO Number | In table 1? | In table 2?



How could I do this in excel such that the comparison table doesn't have duplicates of each row.

I.e. customer number 14 and PO 4 maybe be in both tables, I don't want to copy the data from both tables into a new worksheet as this will create duplicates.

I am racking my brain over this....

Any help appreciated.

Thanks,
Harry
 
Looks like you'll need to get a list of the unique PO's (ie copy all PO's from both tables and paste to a new column, then use the advanced filter unique option) and then do a vlookup in the "in table 1?" and "in table 2?" columns...
 
Looks like you'll need to get a list of the unique PO's (ie copy all PO's from both tables and paste to a new column, then use the advanced filter unique option) and then do a vlookup in the "in table 1?" and "in table 2?" columns...

This, but if you're using 2007+(?) there is a built in Remove Duplicates function on the Data tab. I'm not sure there is a way of doing this without first combining your two tables into a single one, and then using vlookups to work out which of the original tables they're in.
 
Thanks for your help guys.

Ended up following the suggestion of pasting both tables in, using the duplicate remover tool and then two v lookups.

:)
 
Back
Top Bottom