Results 1 to 5 of 5

alternative methods to sumproduct for comparing/matching rows

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-29-2011
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    122

    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.

    =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?
    Last edited by intothewild; 11-17-2012 at 07:11 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1