Comparing columns of data between worksheets and returning value when matched.

Ronanm · May 27, 2019

If I had three columns in one worksheet and three columns in another worksheet to compare. How can I check the 2nd worksheet to find matching records to the 1st worksheet, then pull out the fourth column value of the 2nd worksheet when there's a match?

Ideally with codeor array rather than concatenating and using Vlookups as there would be thousands of records. Thanks



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]FirstName
[/TD]
[TD]SecondName[/TD]
[TD]Dept[/TD]
[TD]FoundID[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Taylor[/TD]
[TD]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]Jones[/TD]
[TD]Blue[/TD]
[TD]712[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]FirstName
[/TD]
[TD]SecondName[/TD]
[TD]Dept[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Walker[/TD]
[TD]Yellow[/TD]
[TD]345[/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]Jones[/TD]
[TD]Blue[/TD]
[TD]712[/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]Jones[/TD]
[TD]Red[/TD]
[TD]999
[/TD]
[/TR]
</tbody>[/TABLE]

skorpionkz · May 27, 2019

Where or how do you want to display matching results?

sandy666 · May 27, 2019

using PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFFFF]Sources[/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFFFFF]Result[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]FirstName[/td][td=bgcolor:#5B9BD5]SecondName[/td][td=bgcolor:#5B9BD5]Dept[/td][td][/td][td][/td][td=bgcolor:#70AD47]FirstName[/td][td=bgcolor:#70AD47]SecondName[/td][td=bgcolor:#70AD47]Dept[/td][td=bgcolor:#70AD47]ID[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Paul[/td][td=bgcolor:#DDEBF7]Taylor[/td][td=bgcolor:#DDEBF7]Red[/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Carol[/td][td=bgcolor:#E2EFDA]Jones[/td][td=bgcolor:#E2EFDA]Blue[/td][td=bgcolor:#E2EFDA]

712

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Carol[/td][td]Jones[/td][td]Blue[/td][td][/td][td][/td][td]Paul[/td][td]Taylor[/td][td]Red[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]FirstName[/td][td=bgcolor:#5B9BD5]SecondName[/td][td=bgcolor:#5B9BD5]Dept[/td][td=bgcolor:#5B9BD5]ID[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Bob[/td][td=bgcolor:#DDEBF7]Walker[/td][td=bgcolor:#DDEBF7]Yellow[/td][td=bgcolor:#DDEBF7]

345

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Carol[/td][td]Jones[/td][td]Blue[/td][td]

712

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Carol[/td][td=bgcolor:#DDEBF7]Jones[/td][td=bgcolor:#DDEBF7]Red[/td][td=bgcolor:#DDEBF7]

999

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code:

[SIZE=1]// Merge1
let
    Source = Table.NestedJoin(Table1,{"FirstName", "SecondName", "Dept"},Table2,{"FirstName", "SecondName", "Dept"},"Table2",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Table2", {"ID"}, {"ID"})
in
    Expand[/SIZE]

skorpionkz · May 27, 2019

VBA solution

Using dictionary might be overkill here, but if there are thousand of entries, you don't want to make this kind of loops.

If you have any questions let me know

Code:

Sub FindMatch()
    
    Dim dic_One     As Object
    Dim dic_Result  As Object
    Dim wsOne       As Worksheet
    Dim wsTwo       As Worksheet
    Dim wsResult    As Worksheet
    Dim l_One       As Long
    Dim l_Two       As Long
    Dim l_Result    As Long
    Dim arr_One     As Variant
    Dim arr_Two     As Variant
    Dim str_Key     As String
    Dim v_Item      As Variant
    
    Set wsOne = ThisWorkbook.Sheets("One")
    Set wsTwo = ThisWorkbook.Sheets("Two")
    Set wsResult = ThisWorkbook.Sheets("Result")
    
    l_One = wsOne.Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    l_Two = wsTwo.Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    
    arr_One = wsOne.Range("A1:D" & l_One)
    arr_Two = wsTwo.Range("A1:D" & l_Two)
    
    Set dic_One = CreateObject("scripting.dictionary")
    Set dic_Result = CreateObject("scripting.dictionary")
    
    For l_One = LBound(arr_One, 1) To UBound(arr_One, 1)
        str_Key = arr_One(l_One, 1) & "||" & arr_One(l_One, 2) & "||" & arr_One(l_One, 3)
        dic_One(str_Key) = arr_One(l_One, 4)
    Next l_One
    
    For l_Two = LBound(arr_Two, 1) To UBound(arr_Two, 1)
        str_Key = arr_Two(l_Two, 1) & "||" & arr_Two(l_Two, 2) & "||" & arr_Two(l_Two, 3)
        If dic_One.exists(str_Key) Then
            dic_Result(str_Key) = arr_Two(l_Two, 4)
        End If
    Next l_Two
    
    For Each v_Item In dic_Result
        If Not v_Item = Empty Then
            l_Result = l_Result + 1
            
            wsResult.Range("A" & l_Result & ":C" & l_Result) = Split(v_Item, "||")
            wsResult.Range("D" & l_Result) = dic_Result(v_Item)
        End If
    Next v_Item
    
End Sub

Ronanm · May 27, 2019

skorpionkz said:

VBA solution

Using dictionary might be overkill here, but if there are thousand of entries, you don't want to make this kind of loops.

If you have any questions let me know

Code:

Sub FindMatch()
    
    Dim dic_One     As Object
    Dim dic_Result  As Object
    Dim wsOne       As Worksheet
    Dim wsTwo       As Worksheet
    Dim wsResult    As Worksheet
    Dim l_One       As Long
    Dim l_Two       As Long
    Dim l_Result    As Long
    Dim arr_One     As Variant
    Dim arr_Two     As Variant
    Dim str_Key     As String
    Dim v_Item      As Variant
    
    Set wsOne = ThisWorkbook.Sheets("One")
    Set wsTwo = ThisWorkbook.Sheets("Two")
    Set wsResult = ThisWorkbook.Sheets("Result")
    
    l_One = wsOne.Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    l_Two = wsTwo.Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    
    arr_One = wsOne.Range("A1:D" & l_One)
    arr_Two = wsTwo.Range("A1:D" & l_Two)
    
    Set dic_One = CreateObject("scripting.dictionary")
    Set dic_Result = CreateObject("scripting.dictionary")
    
    For l_One = LBound(arr_One, 1) To UBound(arr_One, 1)
        str_Key = arr_One(l_One, 1) & "||" & arr_One(l_One, 2) & "||" & arr_One(l_One, 3)
        dic_One(str_Key) = arr_One(l_One, 4)
    Next l_One
    
    For l_Two = LBound(arr_Two, 1) To UBound(arr_Two, 1)
        str_Key = arr_Two(l_Two, 1) & "||" & arr_Two(l_Two, 2) & "||" & arr_Two(l_Two, 3)
        If dic_One.exists(str_Key) Then
            dic_Result(str_Key) = arr_Two(l_Two, 4)
        End If
    Next l_Two
    
    For Each v_Item In dic_Result
        If Not v_Item = Empty Then
            l_Result = l_Result + 1
            
            wsResult.Range("A" & l_Result & ":C" & l_Result) = Split(v_Item, "||")
            wsResult.Range("D" & l_Result) = dic_Result(v_Item)
        End If
    Next v_Item
    
End Sub

Thank you Skorpionkz. It will take a bit for me to understand it, but I am starting to learn about classes, collections and dictionaries so this will be great for me. Appreciate it.
BTW The result will go into the "Found" column as showing in the first table (It gets taken from the 4th Column in the second table. Thanks again...

skorpionkz · May 27, 2019

To have it in Found column code would need to be adjusted a little. It is almost 23pm for me now, let me know if you will figure out how to change the code so it give you result in requested column. If not I will update the code when I get to work and paste it.

skorpionkz · May 28, 2019

Bellow is updated code. Assumptions: first sheet is the source sheet, the second sheet is the sheet to populate with data.

Code:

Option Explicit


Sub FindMatch()
    
    Dim dic_One     As Object
    Dim wsOne       As Worksheet
    Dim wsTwo       As Worksheet
    Dim l_One       As Long
    Dim l_Two       As Long
    Dim l_Result    As Long
    Dim arr_One     As Variant
    Dim arr_Two     As Variant
    Dim arr_Result  As Variant
    Dim str_Key     As String
    
    Set wsOne = ThisWorkbook.Sheets("One")
    Set wsTwo = ThisWorkbook.Sheets("Two")
    
    l_One = wsOne.Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    l_Two = wsTwo.Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    
    arr_One = wsOne.Range("A1:D" & l_One)
    arr_Two = wsTwo.Range("A1:D" & l_Two)
    
    ReDim arr_Result(1 To l_Two, 1 To 1)
    
    
    Set dic_One = CreateObject("scripting.dictionary")
    
    For l_One = LBound(arr_One, 1) To UBound(arr_One, 1)
        str_Key = arr_One(l_One, 1) & "||" & arr_One(l_One, 2) & "||" & arr_One(l_One, 3)
        dic_One(str_Key) = arr_One(l_One, 4)
    Next l_One
    
    For l_Result = LBound(arr_Two, 1) To UBound(arr_Two, 1)
        str_Key = arr_Two(l_Result, 1) & "||" & arr_Two(l_Result, 2) & "||" & arr_Two(l_Result, 3)
        If dic_One.exists(str_Key) Then
            arr_Result(l_Result, 1) = dic_One(str_Key)
        End If
    Next l_Result
    
    arr_Result(1, 1) = "FoundID"
    wsTwo.Range("D1:D" & l_Two) = arr_Result


End Sub

Ronanm · May 28, 2019

That works a treat Andrew. I'm going to wok through it and understand it as well. Thanks again.

Comparing columns of data between worksheets and returning value when matched.

Ronanm

Board Regular

Excel Facts

skorpionkz

Well-known Member

sandy666

Banned - Rules violations

skorpionkz

Well-known Member

Ronanm

Board Regular

skorpionkz

Well-known Member

skorpionkz

Well-known Member

Ronanm

Board Regular

Similar threads

Forum statistics

Share this page

Comparing columns of data between worksheets and returning value when matched.

Board Regular

Excel Facts

Well-known Member

Banned - Rules violations

Well-known Member

Board Regular

Well-known Member

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock