Compare 2 sheets

  • Thread starter Thread starter Tim Rude
  • Start date Start date
T

Tim Rude

I've got 2 sheets, both of which have a column of 1500+ names, followed
by some columns of other info. The list of names in the two sheets are
mostly the same, but not every name will be in each list.

Sheet 1 also contains a 'Flag' column which will have either an "X" in
it or will be blank. List 2 also has the 'Flag' column but all fields in
it are blank.

For every name in Sheet 1 that has a matching name in Sheet 2, I need to
copy over the corresponding 'Flag' value, leaving the other data in
Sheet 2 intact and ignoring any names from Sheet 1 that aren't already
in Sheet 2.

For example:

Sheet 1 contains:
Doe, John X other sheet1 stuff
Jones, Sue other sheet1 stuff
Smith, Bill X other sheet1 stuff <- unique to S1
Wood, Jim X other sheet1 stuff

Sheet 2 contains:
Doe, John other sheet2 stuff
Jones, Sue other sheet2 stuff
Lee, Bob other sheet2 stuff <- unique to S2
Wood, Jim other sheet2 stuff

I need to end up with:
Doe, John X other sheet2 stuff
Jones, Sue other sheet2 stuff
Lee, Bob other sheet2 stuff
Wood, Jim X other sheet2 stuff

How (using Excel 97 or 2000)?
 
I've got 2 sheets, both of which have a column of 1500+ names, followed
by some columns of other info. The list of names in the two sheets are
mostly the same, but not every name will be in each list.

Sheet 1 also contains a 'Flag' column which will have either an "X" in
it or will be blank. List 2 also has the 'Flag' column but all fields in
it are blank.

For every name in Sheet 1 that has a matching name in Sheet 2, I need to
copy over the corresponding 'Flag' value, leaving the other data in
Sheet 2 intact and ignoring any names from Sheet 1 that aren't already
in Sheet 2.

For example:

Sheet 1 contains:
Doe, John X other sheet1 stuff
Jones, Sue other sheet1 stuff
Smith, Bill X other sheet1 stuff <- unique to S1
Wood, Jim X other sheet1 stuff

Sheet 2 contains:
Doe, John other sheet2 stuff
Jones, Sue other sheet2 stuff
Lee, Bob other sheet2 stuff <- unique to S2
Wood, Jim other sheet2 stuff

I need to end up with:
Doe, John X other sheet2 stuff
Jones, Sue other sheet2 stuff
Lee, Bob other sheet2 stuff
Wood, Jim X other sheet2 stuff

How (using Excel 97 or 2000)?

--
Tim Rude

(e-mail address removed)
(remove NOSPAM. for correct email address)
Click to expand...

Assuming the supplied sample data starts in A2 on sheet2 and sheet1,
try this formula in B2 on sheet2, filled down to B2000, or Bwhatever.

=IF(ISNA(VLOOKUP(A2,Sheet1!$A$2:$B$2000,2,FALSE)),"",IF(VLOOKUP(Sheet2!
A2,Sheet1!$A$2:$B$2000,2,FALSE)=0,"",VLOOKUP(Sheet2!A2,Sheet1!$A$2:$B
$2000,2,FALSE)))


Ken Johnson
 
Back
Top