Green Squirrel
New Member
- Joined
- Jan 9, 2021
- Messages
- 25
- Office Version
- 365
- Platform
- 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.
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.
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