Green Squirrel
New Member
- Joined
- Jan 9, 2021
- Messages
- 25
- Office Version
- 365
- Platform
- MacOS
My first question was "How do I match two columns form two different tables". @Akuini was so kind enough to help me with this. But now I struggle with the next step. Which is getting other info from matching lists.
The next issue is how do I get relevant info when there is a match.
Situation
Table1 on sheet1 has 4 columns.
Column1 has as name "Voornaam"
Column2 has as name "Familienaam"
Column3 has as name "DOB"
Column4 has as name "Plaats"
Table2 on sheet2 has also 4 columns.
Column1 has as name "First name"
Column2 has as name "Second name"
Column3 has as name "Geslacht"
Column2 has as name "Woonplaats"
Table3 on sheet3 has 2 columns
Column1 has as name "First name"
Column2 has as name "Leeftijd"
With my script I get the matches that I need.
So I get result when Sheet1.Table1.Column1 matches Sheet2.Table2.Column1 AND when Sheet1.Table1.Column2 matches Sheet2.Table2.Column2
So when there is a match I want to get the values of the columns below.
Sheet1 Table1 column3
Sheet1 Table1 column4
Sheet2 Table2 column3
Sheet2 Tabel2 column4
Sheet3 Table3 column2
The next issue is how do I get relevant info when there is a match.
Situation
Table1 on sheet1 has 4 columns.
Column1 has as name "Voornaam"
Column2 has as name "Familienaam"
Column3 has as name "DOB"
Column4 has as name "Plaats"
Table2 on sheet2 has also 4 columns.
Column1 has as name "First name"
Column2 has as name "Second name"
Column3 has as name "Geslacht"
Column2 has as name "Woonplaats"
Table3 on sheet3 has 2 columns
Column1 has as name "First name"
Column2 has as name "Leeftijd"
With my script I get the matches that I need.
So I get result when Sheet1.Table1.Column1 matches Sheet2.Table2.Column1 AND when Sheet1.Table1.Column2 matches Sheet2.Table2.Column2
So when there is a match I want to get the values of the columns below.
Sheet1 Table1 column3
Sheet1 Table1 column4
Sheet2 Table2 column3
Sheet2 Tabel2 column4
Sheet3 Table3 column2
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