How to merge partial data from two files? $10 Reward!!

Oh to be an Excel genius who just knew how to do things!

I have two files, let's call them (Jack.xls) and (Jill.xls). File Jack has one column (A) with 10,000 rows of text data.

File Jill has 3 columns:
(A) 20,000 rows of text data (mixed into it are the 10,000 rows from Jack.xls)
(B) 20,000 rows of other text data
(C) 20,000 rows of yet more text data

When looking up the file the user needs to see the full row (e.g. row 21, column A, B, C).

I want to somehow extract from file Jill.xls only the data that correlates with the data in Jack.xls. In other words, I want to be left with a file that includes only the 10,000 rows in Jack.xls but also the appropriate (b) and (c) columns from Jill.xls.

Is this straightforward?

Very, VERY many thanks

- Excel Novice

Hey Jaason...

If the data from Jill (A) is the exact same as Jack (A), then you have a pretty simple task ahead of you.

Import the Jack xls file inside a new sheet in Jill xls (giggity?). Save it as something new
In Jill's D1, run this Vlookup:
=Vlookup(A1,Jack!A:A,1,False)
Copy that down for the entirety of the sheet.

Apply the auto-filter using keystroke Alt, d, f, f (one after another is fine)
In Column D, apply the filter to highlight values with #N/A
These are values where there was no successful lookup... That means that they didn't exist within the Jack file. Delete these rows.
You can now remove the filter, remove column D, and the values that are left should only be those that were in both the Jack and Jill files, with all of the data from the Jill file.



If there are unknown problems, one potential is that there are leading spaces in the data in column A. You can use the TRIM function to remove leading and following empty spaces.

Good luck

Hi
As I understand correctly you want to get the column in Jack file with the relevent data in Jill file.
Try this

1. Copy the source column from Jack file in a new file in column A start from A2
2. in B2 enter (To extract contents of column B)
B2:
=VLOOKUP($A2,[jill.xls]Sheet1!$A$2:$C$200,2,FALSE)
copied down
3. in C2 enter (To extract colulmn C)
C2:
=VLOOKUP($A2,[jill.xls]Sheet1!$A$2:$C$200,3,FALSE)
Copied down

Since all the data in Jack file is in Jill file and Jill has extra data, there should be no N/A error

Hope this works

If it dose not help please upload the sample file giving input and expected output
Regards