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.
=SUMPRODUCT(--(Sheet1!$A$2:$A$51=Sheet2!A2),--(Sheet1!$B$2:$B$51=Sheet2!B2),--(Sheet1!$C$2:$C$51=Sheet2!C2),--(Sheet1!$D$2:$D$51=Sheet2!D2))
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?
Bookmarks