Matching two columns from two tables VBA

Green Squirrel

New Member
Joined
Jan 9, 2021
Messages
25
Office Version
  1. 365
Platform
  1. MacOS
Hi,

I have 2 tables in Excel where I'm trying to match two columns for each table.

Table1 on sheet1 has 2 columns.
Column1 has as name "Voornaam"​
Column2 has as name "Familienaam"​
Table2 on sheet2 has also 2 columns.
Column1 has as name "First name"​
Column2 has as name "Second name"​
What I want to achieve:

When the value of Column1("First name") in Table2 on sheet2 is a match with Column1("Voornaam") in Table1 on sheet1 are a MATCH

AND

When the value of Column2("Second name") in Table2 on sheet2 is a match with Column1("Familienaam") in Table1 on sheet1 are a MATCH

Than I want to get the MATCHED results on Sheet 4.

With my current script the 1st part of what I want to do works (When the value of Column1("First name") in Table2 on sheet2 is a match with Column1("Voornaam") in Table1 on sheet1 are a MATCH)
But the matching Column2("Second name") in Table2 on sheet2 with Column1("Familienaam") in Table1 on sheet1 doesn't and I can't figure out why.

VBA Code:
Sub x()

Dim v1(), i As Long

    
    Range("D10:E100").ClearContents
    

    ml1 = Sheets("Sheet1").ListObjects("Table1").ListColumns("Voornaam").DataBodyRange
    ml2 = Sheets("Sheet1").ListObjects("Table1").ListColumns("Familienaam").DataBodyRange
    
    sq1 = Sheets("Sheet2").ListObjects("Table2").ListColumns("First name").DataBodyRange
    sq2 = Sheets("Sheet2").ListObjects("Table2").ListColumns("Second name").DataBodyRange
   
   
  
ReDim v1(1 To UBound(ml1, 1))
ReDim v2(1 To UBound(ml1, 1))

For i = LBound(sq1) To UBound(sq1)

    If Not IsError(Application.Match(sq1(i, 1), ml1, 0)) And Not IsError(WorksheetFunction.Match(sq2(i, 1), ml2, 0)) Then
               
            j = j + 1
            v1(j) = sq1(i, 1)
            v2(j) = sq2(i, 1)
            
            End If
    Next i

    Sheet4.Range("D10").Resize(j) = Application.Transpose(v1)
    Sheet4.Range("E10").Resize(j) = Application.Transpose(v2)

End Sub
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
Try this:

VBA Code:
Sub a1159025a()

Dim i As Long, k As Long
Dim va, vb, v1, v2
    
    va = Sheets("Sheet1").ListObjects("Table1").ListColumns("Voornaam").DataBodyRange.Resize(, 2)
    vb = Sheets("Sheet2").ListObjects("Table2").ListColumns("First name").DataBodyRange.Resize(, 2)
   
    ReDim v1(1 To UBound(va, 1), 1 To 1)
    ReDim v2(1 To UBound(vb, 1), 1 To 1)

    For i = 1 To UBound(va, 1)
        v1(i, 1) = va(i, 1) & "|" & va(i, 2)
    Next
    
    For i = 1 To UBound(va, 1)
        v2(i, 1) = vb(i, 1) & "|" & vb(i, 2)
    Next

ReDim vc(1 To UBound(va, 1), 1 To 2)

For i = LBound(v1) To UBound(v1)

     a = Application.Match(v1(i, 1), v2, 0)
     
     If IsNumeric(a) Then
        k = k + 1
        vc(k, 1) = Split(v2(a, 1), "|")(0)
        vc(k, 2) = Split(v2(a, 1), "|")(1)
     End If

Next i
    
    Sheet4.Range("D10:E100").ClearContents
    Sheet4.Range("D10").Resize(UBound(vc, 1), 2) = vc

End Sub
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,610
Messages
5,625,800
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top