Using LOOKUP macro (VBA)

[catlair]

Hi,

How do I match one data set from one spreadsheet (A) with another
spreadsheet (B) row by row and then delete or highlight on spreadsheet
(A) the data row that matches any row in spreadsheet B, using two
fields as a criteria ('AND' logic) in the search (in spreadsheet B)
via a macro? Will the lookup function be more appropriate than MATCH?

catlair

 

[ShaneDevenshire]

Hi Catlair,

One of the easiest ways is to enter the following formula into the sheet
which you want to mark items that are found in the other workbook. Do this
manually first, then record the code and modify it as needed.

=SUMPRODUCT((A1=[Book3]Sheet1!$A$1:$A$4)*(B1=[Book3]Sheet1!$B$1:$B$4))

In this example the first row you want to check is row 1 and the two
conditions are in A1 and B1. You want to compare these with two ranges in
the other wb. Reference the entire ranges in the second book. Copy the
formula down. Any cells that match both criterial will display a number >0.

Select one of the formulas, turn on the recorder, click the Formula Bar (go
into Edit mode), and press Enter. Stop the recorder.

You will need to modify the macro to enter the formula in the entire range.
And then you need to decide what you want to do with the results.

This method is about 100 times faster than looping through all the cells.

 

[JE McGimpsey]

How do I match one data set from one spreadsheet (A) with another
spreadsheet (B) row by row and then delete or highlight on spreadsheet
(A) the data row that matches any row in spreadsheet B, using two
fields as a criteria ('AND' logic) in the search (in spreadsheet B)
via a macro? Will the lookup function be more appropriate than MATCH?

What is different in this problem statement from the previous one for
which I offered a solution?

Perhaps it would help if you indicated what you mean when you said my
suggestion "Doesn't seem to work".

There is NO function which will highlight or delete rows. LOOKUP will
return values if a match is found, which doesn't seem to be appropriate
here.

You could use conditional formatting to highlight rows, or you could use
a two-step process. You could combine the steps in a macro.

 

[catlair]

Hi,

Basically, I would like to one spreadsheet to lookup the other
spreadsheet, if the criteria matches then extract the information from
the latter spreadsheet and add it into the former spreadsheet in
another cell in that same row.

The solution you had provided though works and is good only provides a
value to indicate if it is matching.

Yeah, am looking into the macro with this formula:
=IF(ISNA(VLOOKUP(B2,"'worksheetB.xls':$C$2:$D$400",2,0)),"Cannot
Find",IF(VLOOKUP(B2,"'worksheetB.xls':$C$2:$D$400",2,0)=E2,"Found","No
Match"))

Am trying to incorporate the LOOKUP or MATCH function to insert a
value.

Thanks!

regards,
catlair