How to fix: compare two sheets code to output ENTIRE ROW matches only?

Question

I have a code that takes two sheets, compares them, and outputs the matches to another sheet. The code works fine but the only problem is that it outputs matches that are from any column. For example; if column A equals "Cab" in sheet 1 & column A equals "Cab" in sheet 2, it outputs the row as a match. What I'm trying to have the code do is check for a FULL ROW match, so that if every value in all columns of a row matches the entire row of the other sheet, then output those rows.

My current code:

Sub CompareSolve()
Dim i As Long
Dim j As Long
Dim n As Long
Dim ar As Variant

ar = Sheet2.Cells(10, 1).CurrentRegion.Value

With CreateObject("Scripting.Dictionary")
.CompareMode = 1
    For i = 2 To UBound(ar, 1)
       .Item(ar(i, 1)) = Empty
    Next

ar = Sheet1.Cells(10, 1).CurrentRegion.Value
n = 1

For i = 2 To UBound(ar, 1)
    If .exists(ar(i, 1)) Then
       n = n + 1
            For j = 1 To UBound(ar, 2)
                   ar(n, j) = ar(i, j)
           Next j
   End If
Next i
End With
Sheet3.Cells(10, 8).Resize(n, UBound(ar, 2)).Value = ar
End Sub

Any ideas on how I can modify this to work?

*EDIT:

before:

after:

Sub CompareSolve()

Dim arr As Variant, wsa As Worksheet, lra As Long, lca As Long
Dim brr As Variant, wsb As Worksheet, lrb As Long, lcb As Long

Set wsa = Sheets(1) 'starting sheet
With wsa
    lra = .Cells(.Rows.Count, 1).End(xlUp).Row
    lca = .Cells(10, .Columns.Count).End(xlToLeft).Column
    arr = .Range(.Cells(10, 1), .Cells(lra, lca)).Value
End With

Set wsb = Sheets(2) 'sheet to match against
With wsb
    lrb = .Cells(.Rows.Count, 1).End(xlUp).Row
    lcb = .Cells(10, .Columns.Count).End(xlToLeft).Column
    brr = .Range(.Cells(10, 1), .Cells(lrb, lcb)).Value
End With

If Not lca = lcb Then Exit Sub
'

Dim i As Long, j As Long, r As Long, k As Long
For r = LBound(arr) To UBound(arr)
    For i = LBound(brr) To UBound(brr)
        For j = 10 To lcb
            If brr(i, j) = arr(r, j) Then
                If j = lca Then wsa.Cells(r, lca + 1).Value = i
                k = 1
                Exit For 'exit j
            Else
                Exit For 'exit j
            End If
        Next j
        If k = 1 Then Exit For 'exit i
    Next i
    k = 0
Next r

End Sub

See @TimWilliams's answer [HERE](https://stackoverflow.com/questions/19395633/how-to-compare-two-entire-rows-in-a-sheet/19396257#19396257) — Siddharth Rout, Aug 05 '19 at 17:13
@SiddharthRout that seems like a completely different code with minimal flexibility. I am looking to edit my current code, which I feel should be a quick easy fix as the code I have is already working, just not to the extent I need it to — aMadNoob, Aug 05 '19 at 17:25
@aMadNoob changing your code to join the contents of all cells in a row to compare string A and string B will still get you back to the approach linked by Sid. See specifically the use of `join()` in Tim's answer to the other post, which can be stored in an array and compared with `match` — Cyril, Aug 05 '19 at 18:33

Cyril · Answer 1 · 2019-08-05T19:19:44.840

1

Can do a slightly different approach to this with variant array and exit for (untested code to give the concept)

Dim arr as variant, wsa as worksheet, lra as long, lca as long
Dim brr as variant, wsb as worksheet, lrb as long, lcb as long
set wsa = sheets(1) 'starting sheet 
with wsa
    lra = .cells(.rows.count,1).end(xlup).row
    lca = .cells(1, .columns.count).end(xltoleft).column
    arr = .range(.cells(1,1),.cells(lra,lca)).value
end with
set wsb = sheets(2) 'sheet to match against
with wsb
    lrb = .cells(.rows.count,1).end(xlup).row
    lcb = .cells(1, .columns.count).end(xltoleft).column
    brr = .range(.cells(1,1),.cells(lrb,lcb)).value
end with
if not lca = lcb then exit sub
'
Dim i as long, j as long, r as long, k as long
for r = lbound(arr) to ubound(arr)
    for i = lbound(brr) to ubound(brr)
        for j = 1 to lcb
            if brr(i,j) = arr(r,j) then 
                If j = lca Then wsa.Cells(r, lca + 1).Value = i
                k = 1
                exit for 'exit j
            else
                exit for 'exit j
            end if
        next j
        if k = 1 then exit for 'exit i
    next i
    k = 0
next r

general concept compare row (r) to row (i) by comparing col/col; if the first column isn't a match, then exit that for loop and move to the next comparison, row (r) and row (i+1).

edit1

Moved a misplaced k = 1 inside the true-scenario of the if-statement to skip to the next r when a match has been found

edited Aug 05 '19 at 19:19

answered Aug 05 '19 at 19:00

Cyril

6,448
1
18
31

Just tested this after the fact and it does work... gg – Cyril Aug 05 '19 at 19:08
trying to modify this to work for my workbook, but I cant seem to get it to work. How would I rewrite this to have it output the results? – aMadNoob Aug 05 '19 at 19:13
@aMadNoob 3 things are of significant consequence... `set wsa = sheets(1)`, `set wsb = sheets(2)`, and `If j = lca Then wsa.Cells(r, lca + 1).Value = i`... the latter of which is where i was outputing the match location. – Cyril Aug 05 '19 at 19:15
@aMadNoob also just noticed i didn't finish a thought when i put the code when it was still untested... the use of `k` was supposed to be in the scenario where all values in the row matched and if so you would `exit for` back to the next `r`. Just updated. – Cyril Aug 05 '19 at 19:16
not sure what I'm doing wrong but when I try to run the code it just adds numbers and other odd values to my Sheet1... – aMadNoob Aug 05 '19 at 19:26
@aMadNoob the last row and last column are based on contiguous ranges in column 1 (A) and row 1, respectively. The `lra` and `lrb` may be a good place to look next. Keep in mind that the overarching hierarchy here is to use variant arrays to capture the data to be compared, so you may need to look at the whole layout to apply this solution. – Cyril Aug 05 '19 at 20:45
I added a before & after picture of what happens when I run your code. I have tried changing every range to what I think it needs to be and nothing works. Please help – aMadNoob Aug 06 '19 at 13:05
@aMadNoob this is all tied to your ranges; your line with the label **Defaults** is not on row 1, therefore you need to adjust all the references to row 1. `lra` and `lrb` will need updated and the loops for `i` and `r` will need to start at the correct row (currently using `lbound()` which means row 1 in this case). – Cyril Aug 06 '19 at 13:36
There is your code(at the end of my question), edited to my best ability to try and make it work for me. I changed all the ranges to start at 10 instead of 1. I run the code and absolutely nothing happens. Can you tell me what I am doing wrong? – aMadNoob Aug 06 '19 at 15:25
@aMadNoob several major issues... since these are based on cell location, you don't want to affect the `arr` and `brr`; the concept isn't changing, just some specific values related to your data, which is purely on your understanding of what is provided. To step back and ask the bird's eye view question, do you understand *what* is happening in the code I provided? As in every line, do you understand each word/aspect? – Cyril Aug 06 '19 at 15:29
`lra` & `lca` and `lrb` & `lcb`, are the values that get the last row and last column in both sheets. `arr` & `brr` are the values that define the starting ranges, which I already tried changing to `(10, 1)` alone and it still does nothing. The second half of the code I have no clue what is really happening but I assume I don't have to change any of those values since there is nothing in there that defines the ranges or anything... – aMadNoob Aug 06 '19 at 15:38
@aMadNoob `arr` and `brr` starting at cells(1,1) is *very* important to this as this means that the cells being written to in the variant array looping are directly related. the `lbound(arr)` in the `for r` loop and `lbound(brr)` in the `for i` loop would be changed to `10`, so you don't affect the relationship between where the source array is and the location of printing to a cell. if you change the source array to start at cells(10,1), rather than cells(1,1), you then need to modify the print location to offset 9 rows. – Cyril Aug 06 '19 at 15:43
@aMadNoob you will very much want to look into how variant arrays work to understand the `lbound`, `ubound`, and the related looping through those arrays. it will make more sense as to why you will be changing the above – Cyril Aug 06 '19 at 15:44
alright, I somewhat see what you're saying but I decided to change my data table to start at 1,1 to make sure this code actually does what I'm trying to do. I ran the code and again, absolutely nothing happens. May I ask what this code is supposed to do? Just to reiterate, I'm looking to get the matches between entire rows in sheet1 & sheet2 and output these matches to sheet3... – aMadNoob Aug 06 '19 at 15:47
@aMadNoob the code will compare a table in sheet 1, stored in `arr`, to a table in sheet 2, stored in 'brr', looping row by row and column by column. if the sheet 1 table row (`r`) has a match, it outputs the row that was matched in sheet 2's table ('i') to the same row that was matched in sheet 1 in the column that is 1 further to the right of the defined last column (`lca+1`). That output is within the looping, `wsa.Cells(r, lca + 1).Value = i`. – Cyril Aug 06 '19 at 15:53
@aMadNoob one thing to keep in mind is the line `if not lca = lcb then exit sub`... since this matches the cell values in every cell within a row, you need to have the same number of columns to compare, or you will NEVER be able to match a whole row. – Cyril Aug 06 '19 at 15:57

How to fix: compare two sheets code to output ENTIRE ROW matches only?

1 Answers1

Linked