compare data in 2 sheets and generate a third one

[RetrogradePAK]

Using Excel 2007

I have a master workbook with person name (column 1)and account number (column 2), there is a second workbook that will contain most but not all names similar to the first one and some will have the account number missing.

What I need is that the workbook 2 is compared with workbook 1 in a work book 3 where the end result would be the same names from workbook 2 and with the all the respective account numbers.

Example:

Workbook 1:
Name Account
AB 121
CV 786
ZZ 919
XR 444

Workbook 2:
Name Account
AB 121
CV
ZZ 919

Workbook 3(Thats what I need after Workbook 1 and 2 are compared)
Name Account
AB 121
CV 786
ZZ 919

 

[SkipVought]

Hi,

You can easily join the data from these two sheet using MS Query in the third sheet to return the joined data. The query SQL would look like this, assuming that

1) your sheets are named Sheet1 & Sheet 2
2) your fields have headings in ROW 1 of Name & Account

So first copy or query each workbook's data into each respective sheet and then query...

SELECT
  `Sheet1$`.Name
, `Sheet1$`.Account
FROM
  `Sheet1$` `Sheet1$`
, `Sheet2$` `Sheet2$`
WHERE `Sheet1$`.Name = `Sheet2$`.Name

Result
[tt]
Name Account
AB 121
CV 786
ZZ 919
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}