• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comparing data sets with common and unique identifiers

A

Austinma

Member
Hi Excel Ninjas


Straight off the back of my inaugural post yesterday I thought I'd come back with another question....


I have two sets of data pertaining to the same unique identifiers (names).


For example First Data set 1


C1 C2

Mark Red

John Yellow

Mike Green

Steve Blue


and Data set 2


C3 C4

Mark Banana

John Apple

Steve Apple

Dan Orange


My question is this:


How can I quickly merge the two data sets to give the following type of thing:


C5 C6 C7

Mark Red Banana

John Yellow Apple

Mike Green [BLANK]

Steve Blue Apple

Dan [BLANK] Orange


If the unique identifers (ie names) were present in both C1 and C3, then I could sort and then simply copy and paste the data next to each other.


The problem comes when one of the names isn't present in one of the identifier columns. I'd like to have a blank cell where the information isn't there - or if i'm being super clever perhaps a 'n/a' inserted in the cell.


My apologies if this has been answered before.


Thanks in advance for all your help and suggestions.


Have a nice weekend,


Regards,


Austinma
 
1. Combine list of names from C1 and C3.

2. Remove duplicates. This is easily done in 2007+ from the Data tab, or you can use the Advanced Filter in 2003.

3. In C6 column, do a VLOOKUP on C1:C2 range.

4. In C7 column, do a VLOOKUP on C3:C4 range.

5. To remove error messages, Ctrl+g, Special, Formulas - errors.

6. Hit the delete key, or type N/A and then press Ctrl+Enter to put value in all selected cells.
 
Hi Luke M,


Thanks for your very quick reply.


I'll give it a go....... and report back what happens


Cheers


Austinma
 
You must log in or register to reply here.
Back
Top