Matching two columns from two tables VBA

[Green Squirrel]

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.

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

 

[Akuini]

Try this:

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

 

[Green Squirrel]

Top! Thank you. Also see where I went wrong now.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.