Compare the same column in 2 workbooks(Excel 2003 SP3)

[steveh]

Good afternoon

I have 2 workbooks Book1 and Book2. Both have town names in column C starting at row 6, Book1 ends at row 1434 and Book2 ends at row 1491.

I would like to be able to compare both columns to identify which town names are in Book2 that are not in Book1 and then append Columns A, B and C from Book2 to the row 1435 etc in Book1

Any ideas please?

[HansV]

Use an empty column in the second (more complete) sheet.
In row 5, enter a tile such as Missing.
In row 6, enter the following formula, substituting the appropriate names:

=ISERROR(MATCH(C6,'[Book1.xls]Sheet1'!$C$6:$C$1434,0))

Added: fill the formula down to row 1491.
Turn on autofilter for this column.
Click on the dropdown arrow in C5 and set it to show only the TRUE values.
Select columns A to C in the filtered rows.
Copy, then switch to the first sheet and paste below the existing data.

[steveh]

Hi Hans

Thank you as usual for your very prompt response. Unfortunately I seem to have mis-interpreted the solution because when I apply the filter I only get false and not a true selection. In the screenshot you can see the formula, the F was added by Excel itself (both books or on a USB drive. F)

Can you spot what I have done wrong here?

[HansV]

Oops, I forgot one essential step: fill the formula down from row 6 to row 1491 (the last filled row). Then filter.

Sorry about that!

[steveh]

Hi Hans

Even an idiot like me should have realised that, doh!! :-)

[steveh]

Oops, I forgot one essential step: fill the formula down from row 6 to row 1491 (the last filled row). Then filter.

Sorry about that!

Hi Hans

As my grandkids say, sweet. Fantastic as usual

Cheers