alternative methods to sumproduct for comparing/matching rows

In VBA i've been using the formula below to compare a row (A2:D2) on Sheet2 with 50 rows (A2:D51) on Sheet1 to see if any of the rows on Sheet1 match my A2:D2 row on Sheet2.

Please Login or Register  to view this content.

If a row on Sheet1 matches the row in Sheet2, the formula returns a 1, if there is no matching row in Sheet1 the formula returns a 0. Although the above method works well, I worry it may return a 1 by mistake, in the case where i have only a few characters/numbers/letters in the rows being compared, and produces a match when there isnt.

Any alternative ways in VBA to compare rows on seperate sheets?

without seeing a sample of what you are working with, its hard to say. but have you tried playing around with vlookup()?

The simplest solution would be to create a helper column on each sheet. The formula in each helper column would be =A2&"|"&B2&"|"&C2&"|"&D2 to collate the data.
Then you can use COUNTIF or VLOOKUP to verify the data on sheet 1 against sheet 2. I suspect that would be much quicker than a complex SUMPRODUCT though I have no reason to believe that SUMPRODUCT won't work.

Regards, TMS

Originally Posted by FDibbins

without seeing a sample of what you are working with, its hard to say. but have you tried playing around with vlookup()?

sample1.xls

sure, file attached. usually try to avoid v-lookups, keep getting problems with them when looking across multiple sheets.

in column E on both sheets, combine the data using =A2&B2&C2&D2, copied down

then in sheet2, column F, use this, copied down...
=IF(ISERROR(VLOOKUP(E2,Yesterday!E2:$E$50,1,FALSE)),"",1)

the resulting 1's would be the "found"