Match and transfer

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:47 PM GMT

Question

I have twofiles. One is the main file and the other is a file containing misc data. I need to match the first file column A (Name) in field to second file and transfer. Need to see where the names overlap in each file. Any ideas.

Answer

Assume both files have a unique identifier in column A and you want to extract or copy information from the first file into the second when there is a match between the files.

File1:

filename: wbook1.xls sheetname: Data Data in A2:F30


File 2: filename: wbook2.xls sheetname: Report Data only in column A A2:A15

in B2 of Report put in the formula

=if(countif([wBook1.xls]Sheet1!$A:$A,$A2)=0,"",Vlookup($A2,[wBook1.xls]Sheet1!$A$2:$F$30,column(),False))


then drag fill this across sheet Report to column F.

Now select B2:F2 and drag fill down to row 15.

where there are matches, the data should be transferred.

Hope that is what you wanted.

Not if you have spaces in the workbook name such as wBook 1.xls or Data 1 as the sheet name you would use a reference like

'[wBook 1.xls]Data 1'!$A:$A

If that isn't what you wanted send sample workbook to twogilvy@msn.com with a clear explanation /claification of what you want.

Advertisement

©2021 eLuminary LLC. All rights reserved.