Comparing two sheets to find common records

Hi all,

This is my first post here and I'm real excited to be part of this forum. The reason I even found this forum is - I've been tasked by my VP of Sales to take two different spreadsheets, lets call them:

Sheet A - a database of potential clients downloaded from Hoovers
Sheet B - a database downloaded from an email list vendor

Sheet A is the primary database and holds all the information for each prospect, except for email address. Hence he bought and downloaded Sheet B and now wants me to merge the two. I realized that the common column in the two is "Phone Number" and so using the phone number I need to find compare all the records in both sheets and add the "email" field/column to Sheet A.

Is there a way to do this? I tried searching for "compare sheets", "merge sheets" on this forum and came across many vlookup, if, match commands and tried tweaking it but was unable to find a solution.

Please help!

Thanks alot in advance!

Hi,

The easiest way to do this is probably using VLOOKUP. I assume you will only want to bring in data if an exact match is found so be sure to specify FALSE as the 4th argument or you could get some interesting results.

Posting a sample workbook with some data for both sheets would help a lot.

Dom

Thanks Dom,

Please find attached two sample sheets as explained. The primary (common) key i need to use to merge the relevant (matching) records is the phone number (Clumn H in Sheet A and Column C in Sheet B).

I'd really appreciate if you could provide with a sample code in a third sheet so i can migrate the concept to the actual data.

Apprecaite it!!

Pop this formula in cell AF2 on sheet A and copy down:

=VLOOKUP(H2,'sheet B'!$C$2:$D$893,2,FALSE)

The VLOOKUP will return #N/A if a match is not found. You can just copy paste the results and get rid of the #N/A using Find...Replace.

Dom

Originally Posted by Domski

Pop this formula in cell AF2 on sheet A and copy down:

=VLOOKUP(H2,'sheet B'!$C$2:$D$893,2,FALSE)

The VLOOKUP will return #N/A if a match is not found. You can just copy paste the results and get rid of the #N/A using Find...Replace.

Dom

Woohoo... thanks Dom... Kudos!! Have added rep bro...

Do I dare push my luck by asking how would I change the formula if I wanted to further enhance my matching/comparing of these datasets; say like matching phone and first and last name. Is that even possible? I mean I am just trying to retool my c++ thought process here.. so is there and && statement which I could use in vlookup to enrich my results?

Thanks a ton again!
kt

PS: Also if you could point me in the right direction as to what'd be a good place to start learning about advanced excel formulas,macros and pivot tables,that'll be swell

You can do it a couple of ways. Add a helper column to your lookup table concatenating the items you want to look up and then use vlookup as before or you can use both array and non-array formula to pass multiple criteria to a lookup. I've attached a sample workbook that shows how all would work.

I learnt pretty much everything I know by answering questions on and reading forums like this one. I'm not much of one for picking up a book and learning like that, much prefer to try things out in real world situations.

If you want books you could do worse than one by John Walkenbach or Bill Jelen.

Hope it helps,

Dom

Thanks Dom!! Highly appreciate it...