Matching data from one spreadsheet to another

[catlair]

Hi,

How do I match one data from one spreadsheet (A) with another (B) row
by row and then delete on spreadsheet (A) the data row that matches a
row from two columns in spreadsheet B?

catlair

 

[JE McGimpsey]

Hi,

How do I match one data from one spreadsheet (A) with another (B) row
by row and then delete on spreadsheet (A) the data row that matches a
row from two columns in spreadsheet B?

One way:

I assume that by different spreadsheets you mean different workbooks.

In an unused column in spreadsheet A array-enter (CMD-RETURN) this in
row 1, then copy down as far as necessary:

=IF(ISNA(MATCH(A1 & B1, '[spreadsheet B.xls]Sheet1'!$A1:$A1000 &
'[spreadsheet B.xls]Sheet1'!$B1:$B1000, 0)), "", "Duplicate")

You can then filter spreadsheet A on that column (e.g., choose
non-blanks). Choose Edit/Goto/Special, Visible Cells Only. Choose
Edit/Delete Row.

 

[catlair]

Hi,

Doesn't seem to work. Am thinking perhaps I am going about this the
wrong way or have not articulated my problem well enough. I suspect
that VLOOKUP VBA may be the answer to my problem. Will repost my
query. Thanks!

regards,
catlair

 

[JE McGimpsey]

Doesn't seem to work. Am thinking perhaps I am going about this the
wrong way or have not articulated my problem well enough. I suspect
that VLOOKUP VBA may be the answer to my problem. Will repost my
query. Thanks!

There's no "VLOOKUP VBA" function - though VBA can access the XL
worksheet function VLOOKUP(). But you can use it as a worksheet function
in a cell, too.

 

[catlair]

Used the following:

=IF(ISNA(VLOOKUP(E2,"[myworkbookB.xls]sheet1!$B$2:$B$349", 0)), "",
"NO MATCH")

However, the result is always the same for all the cells. It does not
seem possible. I think I am missing something?

catlair

 

[Bob Greenblatt]

Used the following:

=IF(ISNA(VLOOKUP(E2,"[myworkbookB.xls]sheet1!$B$2:$B$349", 0)), "",
"NO MATCH")

However, the result is always the same for all the cells. It does not
seem possible. I think I am missing something?

catlair

This line of code says: if the Vlookup doesn't find anything, make the value
null (""), but, if Vlookup DOES NOT return #N/A, meaning it found something,
then set the value to "No Match".

This doesn't sound like quite what you want to do. Therefore, I would say
yes, you are missing something. So, I think what you really want is:

=IF(ISNA(VLOOKUP(E2,"[myworkbookB.xls]sheet1!$B$2:$B$349", 0)), "No Match",
VLOOKUP(E2,"[myworkbookB.xls]sheet1!$B$2:$B$349", 0))

 

[Geoff Lilley]

Used the following:

=IF(ISNA(VLOOKUP(E2,"[myworkbookB.xls]sheet1!$B$2:$B$349", 0)), "",
"NO MATCH")

However, the result is always the same for all the cells. It does not
seem possible. I think I am missing something?

catlair

This line of code says: if the Vlookup doesn't find anything, make the value
null (""), but, if Vlookup DOES NOT return #N/A, meaning it found something,
then set the value to "No Match".

This doesn't sound like quite what you want to do. Therefore, I would say
yes, you are missing something. So, I think what you really want is:

=IF(ISNA(VLOOKUP(E2,"[myworkbookB.xls]sheet1!$B$2:$B$349", 0)), "No Match",
VLOOKUP(E2,"[myworkbookB.xls]sheet1!$B$2:$B$349", 0))

Bob:

Not meaning to butt in here, but where's the column number in that
formula? I take it the use of the "0" is for the "match_type"?

If that's the case:
=IF(ISNA(VLOOKUP(E2,[my workbookB.xls]sheet1!$B$2:$B$349,1,0)),"No
Match",VLOOKUP(E2,[my workbookB.xls]sheet1!$B$2:$B$349,1,0))

Sorry if I'm off base here. Just hope that might help.

Cheers
Geoff