Help with Excel

xabi

I have two spreasheets and need to copy data from one to the other based on a names list.

Sheet 1 - A = Firstname, B = Lastname
Sheet 2 - A = Firstname, B = Lastname, C = Loaction, D= Manager, etc..

So i need to use data in column A and B from Sheet 1, search sheet 2 for a match and bring C and D data back to sheet 1.

Help!

TheChizler

Read the help file for vlookup, it should be able to do what you want. You may need to use a 'helper' column where A and B are concatenated and then perform the match on the helper column. I'm sure there are clever ways around it but that should be straightforward enough.

xabi

So, a new column in both sheets with Firstname Lastname (in the same cell) and then use this as the search criteria

Akrasia

You can do V lookup but that can be a bit confusing if there are duplicate entries and if the data is saved in multiple cells on the spreadsheet. (first and 2nd name)

It seems to me like you have a master spreadsheet with a list of all the customers, and a second list with a sub section of customers, and you want to get the details of only the sub section of customers

The first thing I'd do is concatenate column A and B so that first and last name are in the same cell


in an empty column on both sheets write this formula
=a1&" "&b1

This will take the first and second name and join them with a space in between so you can compare the two sheets more easily

Flash fill this formula the whole way down the list so that every name on both lists are concatenated.

Then I'd check if there are duplicates on that cell in sheet 1 and sheet 2 (use conditional formatting to highlight duplicates). If there are duplicates, you'll need to know this and account for differences in the customer records (maybe a customer id number or something) (maybe you want to have multiple rows per name in sheet 2, that's fine too, just leave the original data there there but delete the names from the concatenated cells because this will affect the matching step later on.)

Once you are satisfied that there are no duplicates, the next thing I would do is copy the list of concatenated names from sheet 1 to the end of the concatenated names in list 2 so you have a long list of all the unique names on each sheet

Do conditional formatting again, this time the duplicates are only the matches between the two sheets, using filters, select only the colour you chose using the conditional formatting for the duplicates, and you have a list of the names that only appear on both sheets as well as the associated details related to each name.

xabi

Thanks,

Sheet one contains duplicate names but i need to keep these as they have different dates for each duplication of name, here is the VLOOKUP im trying =VLOOKUP(H2,'[2nd sheet.xlsx]Sheet1'!$A$132:$A$33999, 23, FALSE).

H2 = concatenate names
A in 2nd sheet is concatenate names
23 = is column W in 2nd sheet that contains the data i want

Where am i going wrong

xabi

got it now, I was only searching column A in 2nd sheet, searching the whole sheet sorted it.