I have two tables in one spreadsheet. both have the same columns - Name, City, Province. My goal is to compare both and if three out of three values in a row match, then pull "Yes", if not, pull "No". I compare rows with rows in these two tables (not random cells).
I have not found a proper formula, so probably need to code it.
I have found a good code, but it works only for looking at the same values in one array. I hope it could be adapted to my problem. Or maybe I need another one.
Sub Compare()
Dim row As Integer
row = 2
Dim firstColumn As String
firstColumn = "H"
Dim lastColumn As String
lastColumn = "J"
Dim resultsColumn As String
resultsColumn = "M"
Dim isFoundText As String
isFoundText = "YES"
Dim isNotFoundText As String
isNotFoundText = "NO"
Do While Range("B" & row).Value <> ""
Dim startChar As Integer
startChar = Asc(firstColumn)
Dim endChar As Integer
endChar = Asc(lastColumn)
Dim i As Integer
Dim hasMatch As Boolean
hasMatch = False
For i = startChar To endChar
If Range(Chr(i) & row).Value = Range(Chr(i + 1) & row).Value Then
hasMatch = True
End If
If Range(Chr(startChar) & row).Value = Range(Chr(i + 1) & row).Value Then
hasMatch = True
End If
Next i
If (hasMatch) Then
Range(resultsColumn & row).Value = isFoundText
Else
Range(resultsColumn & row).Value = isNotFoundText
End If
row = row + 1
Loop
End Sub
For this type of task it's better to move that data to Variant Arrays and do the looping over those (much faster). Also, the the pattern matching can be generalised away from the data, making for a more reusable solution and seperation of concerns
The Compare Function
Private Function CompareColumns(Table1 As Range, Table2 As Range, ColPairs() As Variant, Optional IsMatch As Variant = True, Optional NoMatch As Variant = False) As Variant
Dim Table1Data As Variant
Dim Table2Data As Variant
Dim OutputData As Variant
Dim rw1 As Long, rw2 As Long
Dim Col As Long
Dim FoundMatch As Boolean
' Move data to variant arrays
Table1Data = Table1.Value2
Table2Data = Table2.Value2
' Size return array
ReDim OutputData(1 To UBound(Table1Data, 1), 1 To 1)
' Loop the arrays
For rw2 = 1 To UBound(Table2Data, 1)
OutputData(rw2, 1) = NoMatch ' initialise
For rw1 = 1 To UBound(Table1Data, 1)
FoundMatch = True
For Col = LBound(ColPairs, 1) To UBound(ColPairs)
If Table1Data(rw1, ColPairs(Col, 1)) <> Table2Data(rw2, ColPairs(Col, 2)) Then
FoundMatch = False ' column not a match, move to next row
Exit For
End If
Next
If FoundMatch Then ' found a match
OutputData(rw2, 1) = IsMatch
Exit For ' exit Table2 loop when match found
End If
Next
Next
' Return result to caller
CompareColumns = OutputData
End Function
Use it like this
Sub Compare()
Dim ws As Worksheet
Dim Table1 As Range
Dim Table2 As Range
Dim Output As Range
Dim OutputTable As Variant
Dim ColPairs() As Variant
Set ws = ActiveSheet ' update to suit your needs
' Set up ranges by any means you choose
With ws
Set Table1 = .Range(.Cells(2, 1), .Cells(.Rows.Count, 3).End(xlUp))
Set Table2 = .Range(.Cells(2, 10), .Cells(.Rows.Count, 8).End(xlUp))
Set Output = .Cells(2, 13).Resize(Table2.Rows.Count, 1)
End With
'Specify columns to compare
ReDim ColPairs(1 To 3, 1 To 2)
ColPairs(1, 1) = 1: ColPairs(1, 2) = 3
ColPairs(2, 1) = 2: ColPairs(2, 2) = 2
ColPairs(3, 1) = 3: ColPairs(3, 2) = 1
' Call Match function
OutputTable = CompareColumns(Table1, Table2, ColPairs, "Yes", "No")
' Place Output on sheet
Output = OutputTable
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments