Cross Reference 2 columns in seperate workbooks

P

Pcakes

Hi, I am trying to determine if I can cross reference 2 columns in seperate
workbooks looking for duplicates. The workbooks are not identical.

Work book 1 would be column A
Worbook 2 would be column B

I would like to delete the rows in Workbook 2 if the data in the 2 columns
in both workbooks match.

Any ideas?

Pcakes
 
G

Gary Brown

In workbook 2, create a 'helper' column and put a vlookup function something
like...
=VLOOKUP(B2,[My1stWorkbook.xls]MySheet1!$A:$A,1,FALSE)
if the formula does NOT return an '#N/A', delete it as it found a match in
workbook 1.
--
HTH,
Gary Brown
[email protected]
If this post was helpful to you, please select ''YES'' at the bottom of the
post.
 
S

ShaneDevenshire

First determine if each row is matching:

=countif(Book1!A$1:A$1000,B1)

If this returns 1 you have a match.

If you are using 2002+ you can then highlight the countif column and press
Ctrl F and enter 1 and set it to search for values and then choose Find All.
Select all the results in the bottom pane and close the Find box. Press Ctrl
- (minus) and choose entire row (assuming that will work for you). If you
are using an earlier version let us know.
 
Top