Merging Two Files

[pf]

I have two spreadsheets with about 10 columns each. In the two files, 8 of
the columns are contain the same data, while the last two columns in each
file has unique data. The only difference in the two files are basically the
last to columns. File A has two unique columns and file B has two unique
columns. I need to create one file that compares 4 of the common columns in
each file, finding a match would copy all the common columns, plus the 2
unique columns from both files into the new file.
How can I accomplish this?

thanks in advance for any help offered...

 

[Bernie Deitrick]

Open both files, and in one file, use

=SUMPRODUCT(('[OtherFile.xls]Other Sheet'!A2:A1000=A2)*('[OtherFile.xls]Other
Sheet'!B2:B1000=B2)*('[OtherFile.xls]Other Sheet'!C2:C1000=C2)*('[OtherFile.xls]Other
Sheet'!D21000=D2)* ROW(A2:A1000))

which will return the row of the 4 column match. Then use

=INDEX('[OtherFile.xls]Other Sheet'!E2:E1000,Cell with the formula above)
=INDEX('[OtherFile.xls]Other Sheet'!F2:F1000,Cell with the formula above)

to extract the matching data from column E and F (and any other columns that you need - but you
should only need two.....)

Obviously, change the names and ranges to match your actual layout.

HTH,
Bernie
MS Excel MVP