Matching to columns from two tables and get info from other columns/tables VBA

Green Squirrel

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

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
 
Just change this line:
vb = Sheets("Sheet2").ListObjects("Table2").DataBodyRange

to this:
vb = Sheets("Sheet2").ListObjects("Table2").DataBodyRange.Offset(, 1)
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I've actually spotted something that isn't correct. But maybe it's because of the way I've explained.

Right now the results are :

SHEET1.TABLE1. COLUMN1 and SHEET1.TABLE1. COLUMN2
Need to match
SHEET2.TABLE2. COLUMN1 and SHEET2.TABLE2. COLUMN2

But it shouldn't be like this. Like I've said. Maybe I've explained wrong.

Should be like this:

If SHEET2.TABLE2. COLUMN1 matches SHEET1.TABLE1. COLUMN1

AND

if SHEET2.TABLE2. COLUMN2 matches SHEET1.TABLE1. COLUMN1

Than I should get the matches as result.
 
Upvote 0
Could you explain the difference by using an example?
 
Upvote 0
Could you explain the difference by using an example?
Right now Columns 1 & 2 from Sheet2.table2 need to match Columns 1 & 2 from Sheet1.table1 before we get matching results.

But this not what I'm trying to achieve.

Columns 1 from Sheet2.table2 needs to match Columns 1 from Sheet1.table1

AND

Columns 2 from Sheet2.table2 needs to match Columns 1 from Sheet1.table1

You see the difference? The way it is right now everything needs to match. But I want the columns to be matched seperate and nod joined
 
Upvote 0
Could you post an example as table including the result as expected?
 
Upvote 0
Ok, the data is a different but the idea stays the same.

SHEET1 - TABLE1
Screenshot 2021-01-21 at 13.38.51.png


SHEET2 - TABLE2
Screenshot 2021-01-21 at 13.48.00.png


SHEET3 - TABLE3 & TABLE4
Screenshot 2021-01-21 at 13.43.45.png




So the idea is the following.

The info on SHEET2.TABLE2 are the games that are played that day. But I don't want all the games. I only want the games if the team is in SHEET1.TABLE1
Let's use the first game out of SHEET2.TABLE2 --> Anderlecht vs Kortrijk.

In order for me to see this game in SHEET4 (The result sheet) the following needs to happen.

1) Need to check if "Anderlecht" is in SHEET1.TABLE1
2) Need to check if "Kortrijk" is in SHEET1.TABLE1

If both criteria are met than I want to show this match on SHEET4.

What info is needed when there is a match?

Date --> SHEET2.TABLE2.COLUMN1
League --> SHEET1.TABLE1.COLUMN2 --> In this example its "Belgium - Jupiler League
Home team --> In this example its "Anderlecht"
Away team --> In this example its "Kortrijk"
Home League --> SHEET1.TABLE1.COLUMN3 --> In this example its "BEhome" (This should be linked to Anderlecht in this example)
Away League --> SHEET1.TABLE1.COLUMN4 --> In this example its "BEaway" (This should be linked to Kortrijk in this example)

Than A little special. We also need data from the two tables in SHEET3.
The first table you see is called "BEhome"
The second table you see is called "BEaway"

As "Anderlecht" is the home team so I need to lookup Anderlecht in SHEET3.BEhome and return Columns CF and GA. For "Anderlecht" this should be 1 and 11
As "Kortijk" is the away team so I need to lookup Kortrijk in SHEET3.BEaway and return Columns CF and GA. For "Kortrijk" this should be 20 and 10

Hope this is clear. Sorry for the Bold text but can't turn it off.

 

Attachments

  • Screenshot 2021-01-21 at 13.38.59.png
    Screenshot 2021-01-21 at 13.38.59.png
    32.7 KB · Views: 7
  • Screenshot 2021-01-21 at 13.39.08.png
    Screenshot 2021-01-21 at 13.39.08.png
    28.8 KB · Views: 9
  • Screenshot 2021-01-21 at 13.38.51.png
    Screenshot 2021-01-21 at 13.38.51.png
    64.2 KB · Views: 8
Upvote 0
Could you post the data as table not as image, so I can copy the data to my worksheet.
The best way is using XL2BB add-in or just copy your range in excel then paste in reply window.
 
Upvote 0
Could you post the data as table not as image, so I can copy the data to my worksheet.
The best way is using XL2BB add-in or just copy your range in excel then paste in reply window.
SHEET1.TABLE1

Excel Formula:
[TABLE]
[TR]
[TD]Team[/TD]
[TD]League[/TD]
[TD]Home League[/TD]
[TD]Away Leagu[/TD]
[/TR]
[TR]
[TD]Anderlecht[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Antwerp[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Beerschot-Wil.[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Beveren[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Cercle Brugge[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Charleroi[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Club Brugge[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Eupen[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Gent[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Kortrijk[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]KRC Genk[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]KV Mechelen[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Mouscron[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]OH Leuven[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Oostende[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Sint-Truiden[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Standard Liege[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[TR]
[TD]Zulte-Waregem[/TD]
[TD]Belgium - Jupiler League[/TD]
[TD]BEhome[/TD]
[TD]BEaway[/TD]
[/TR]
[/TABLE]


SHEET2.TABLE2
Excel Formula:
[TABLE]
[TR]
[TD]Date[/TD]
[TD]Home[/TD]
[TD]Away[/TD]
[/TR]
[TR]
[TD]Tue 21 Jan 20:00[/TD]
[TD]Anderlecht[/TD]
[TD]Kortrijk[/TD]
[/TR]
[TR]
[TD]Tue 19 Jan 20:00[/TD]
[TD]Antwerp[/TD]
[TD]KRC Genk[/TD]
[/TR]
[TR]
[TD]Tue 19 Jan 20:00[/TD]
[TD]Beerschot-Wil.[/TD]
[TD]KV Mechelen[/TD]
[/TR]
[TR]
[TD]Tue 19 Jan 20:00[/TD]
[TD]Beveren[/TD]
[TD]Mouscron[/TD]
[/TR]
[TR]
[TD]Tue 19 Jan 20:00[/TD]
[TD]Cercle Brugge[/TD]
[TD]OH Leuven[/TD]
[/TR]
[TR]
[TD]Tue 19 Jan 20:00[/TD]
[TD]Charleroi[/TD]
[TD]Oostende[/TD]
[/TR]
[TR]
[TD]Tue 20 Jan 20:00[/TD]
[TD]Club Brugge[/TD]
[TD]Sint-Truiden[/TD]
[/TR]
[TR]
[TD]Tue 19 Jan 20:00[/TD]
[TD]Eupen[/TD]
[TD]Standard Liege[/TD]
[/TR]
[TR]
[TD]Tue 19 Jan 20:00[/TD]
[TD]Gent[/TD]
[TD]Zulte-Waregem[/TD]
[/TR]
[TR]
[TD]Tue 19 Jan 20:00[/TD]
[TD]AC Milan[/TD]
[TD]Sampdoria[/TD]
[/TR]
[TR]
[TD]Tue 19 Jan 20:00[/TD]
[TD]Lazio[/TD]
[TD]AS Roma[/TD]
[/TR]
[/TABLE]

SHEET3.TABLE("BEhome")
Excel Formula:
[TABLE]
[TR]
[TD]2[/TD]
[TD]GF[/TD]
[TD]GA[/TD]
[/TR]
[TR]
[TD]Anderlecht[/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]11[/RIGHT][/TD]
[/TR]
[TR]
[TD]Antwerp[/TD]
[TD][RIGHT]2[/RIGHT][/TD]
[TD][RIGHT]12[/RIGHT][/TD]
[/TR]
[TR]
[TD]Beerschot-Wil.[/TD]
[TD][RIGHT]3[/RIGHT][/TD]
[TD][RIGHT]13[/RIGHT][/TD]
[/TR]
[TR]
[TD]Beveren[/TD]
[TD][RIGHT]4[/RIGHT][/TD]
[TD][RIGHT]14[/RIGHT][/TD]
[/TR]
[TR]
[TD]Cercle Brugge[/TD]
[TD][RIGHT]5[/RIGHT][/TD]
[TD][RIGHT]15[/RIGHT][/TD]
[/TR]
[TR]
[TD]Charleroi[/TD]
[TD][RIGHT]6[/RIGHT][/TD]
[TD][RIGHT]16[/RIGHT][/TD]
[/TR]
[TR]
[TD]Club Brugge[/TD]
[TD][RIGHT]7[/RIGHT][/TD]
[TD][RIGHT]17[/RIGHT][/TD]
[/TR]
[TR]
[TD]Eupen[/TD]
[TD][RIGHT]8[/RIGHT][/TD]
[TD][RIGHT]17[/RIGHT][/TD]
[/TR]
[TR]
[TD]Gent[/TD]
[TD][RIGHT]9[/RIGHT][/TD]
[TD][RIGHT]19[/RIGHT][/TD]
[/TR]
[TR]
[TD]Kortrijk[/TD]
[TD][RIGHT]10[/RIGHT][/TD]
[TD][RIGHT]20[/RIGHT][/TD]
[/TR]
[TR]
[TD]KRC Genk[/TD]
[TD][RIGHT]11[/RIGHT][/TD]
[TD][RIGHT]21[/RIGHT][/TD]
[/TR]
[TR]
[TD]KV Mechelen[/TD]
[TD][RIGHT]12[/RIGHT][/TD]
[TD][RIGHT]22[/RIGHT][/TD]
[/TR]
[TR]
[TD]Mouscron[/TD]
[TD][RIGHT]13[/RIGHT][/TD]
[TD][RIGHT]23[/RIGHT][/TD]
[/TR]
[TR]
[TD]OH Leuven[/TD]
[TD][RIGHT]14[/RIGHT][/TD]
[TD][RIGHT]24[/RIGHT][/TD]
[/TR]
[TR]
[TD]Oostende[/TD]
[TD][RIGHT]15[/RIGHT][/TD]
[TD][RIGHT]25[/RIGHT][/TD]
[/TR]
[TR]
[TD]Sint-Truiden[/TD]
[TD][RIGHT]16[/RIGHT][/TD]
[TD][RIGHT]26[/RIGHT][/TD]
[/TR]
[TR]
[TD]Standard Liege[/TD]
[TD][RIGHT]17[/RIGHT][/TD]
[TD][RIGHT]27[/RIGHT][/TD]
[/TR]
[TR]
[TD]Zulte-Waregem[/TD]
[TD][RIGHT]18[/RIGHT][/TD]
[TD][RIGHT]28[/RIGHT][/TD]
[/TR]
[/TABLE]

SHEET3.TABLE("BEaway")
Excel Formula:
[TABLE]
[TR]
[TD]2[/TD]
[TD]GF[/TD]
[TD]GA[/TD]
[/TR]
[TR]
[TD]Anderlecht[/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]11[/RIGHT][/TD]
[/TR]
[TR]
[TD]Antwerp[/TD]
[TD][RIGHT]2[/RIGHT][/TD]
[TD][RIGHT]12[/RIGHT][/TD]
[/TR]
[TR]
[TD]Beerschot-Wil.[/TD]
[TD][RIGHT]3[/RIGHT][/TD]
[TD][RIGHT]13[/RIGHT][/TD]
[/TR]
[TR]
[TD]Beveren[/TD]
[TD][RIGHT]4[/RIGHT][/TD]
[TD][RIGHT]14[/RIGHT][/TD]
[/TR]
[TR]
[TD]Cercle Brugge[/TD]
[TD][RIGHT]5[/RIGHT][/TD]
[TD][RIGHT]15[/RIGHT][/TD]
[/TR]
[TR]
[TD]Charleroi[/TD]
[TD][RIGHT]6[/RIGHT][/TD]
[TD][RIGHT]16[/RIGHT][/TD]
[/TR]
[TR]
[TD]Club Brugge[/TD]
[TD][RIGHT]7[/RIGHT][/TD]
[TD][RIGHT]17[/RIGHT][/TD]
[/TR]
[TR]
[TD]Eupen[/TD]
[TD][RIGHT]8[/RIGHT][/TD]
[TD][RIGHT]17[/RIGHT][/TD]
[/TR]
[TR]
[TD]Gent[/TD]
[TD][RIGHT]9[/RIGHT][/TD]
[TD][RIGHT]19[/RIGHT][/TD]
[/TR]
[TR]
[TD]Kortrijk[/TD]
[TD][RIGHT]10[/RIGHT][/TD]
[TD][RIGHT]20[/RIGHT][/TD]
[/TR]
[TR]
[TD]KRC Genk[/TD]
[TD][RIGHT]11[/RIGHT][/TD]
[TD][RIGHT]21[/RIGHT][/TD]
[/TR]
[TR]
[TD]KV Mechelen[/TD]
[TD][RIGHT]12[/RIGHT][/TD]
[TD][RIGHT]22[/RIGHT][/TD]
[/TR]
[TR]
[TD]Mouscron[/TD]
[TD][RIGHT]13[/RIGHT][/TD]
[TD][RIGHT]23[/RIGHT][/TD]
[/TR]
[TR]
[TD]OH Leuven[/TD]
[TD][RIGHT]14[/RIGHT][/TD]
[TD][RIGHT]24[/RIGHT][/TD]
[/TR]
[TR]
[TD]Oostende[/TD]
[TD][RIGHT]15[/RIGHT][/TD]
[TD][RIGHT]25[/RIGHT][/TD]
[/TR]
[TR]
[TD]Sint-Truiden[/TD]
[TD][RIGHT]16[/RIGHT][/TD]
[TD][RIGHT]26[/RIGHT][/TD]
[/TR]
[TR]
[TD]Standard Liege[/TD]
[TD][RIGHT]17[/RIGHT][/TD]
[TD][RIGHT]27[/RIGHT][/TD]
[/TR]
[TR]
[TD]Zulte-Waregem[/TD]
[TD][RIGHT]18[/RIGHT][/TD]
[TD][RIGHT]28[/RIGHT][/TD]
[/TR]
[/TABLE]
 
Upvote 0
Don't paste in Excel function tag, just paste without any tag, like this;

STATECITYREP
ArizonaAnchorage
ArizonaApache JunctionAxel
ArizonaAuburnAaliyah
ArizonaAuburnAxel
ArizonaBirminghamAxel
 
Upvote 0
SHEET1.TABLE1
TeamLeagueHome LeagueAway League
AnderlechtBelgium - Jupiler LeagueBEhomeBEaway
AntwerpBelgium - Jupiler LeagueBEhomeBEaway
Beerschot-Wil.Belgium - Jupiler LeagueBEhomeBEaway
BeverenBelgium - Jupiler LeagueBEhomeBEaway
Cercle BruggeBelgium - Jupiler LeagueBEhomeBEaway
CharleroiBelgium - Jupiler LeagueBEhomeBEaway
Club BruggeBelgium - Jupiler LeagueBEhomeBEaway
EupenBelgium - Jupiler LeagueBEhomeBEaway
GentBelgium - Jupiler LeagueBEhomeBEaway
KortrijkBelgium - Jupiler LeagueBEhomeBEaway
KRC GenkBelgium - Jupiler LeagueBEhomeBEaway
KV MechelenBelgium - Jupiler LeagueBEhomeBEaway
MouscronBelgium - Jupiler LeagueBEhomeBEaway
OH LeuvenBelgium - Jupiler LeagueBEhomeBEaway
OostendeBelgium - Jupiler LeagueBEhomeBEaway
Sint-TruidenBelgium - Jupiler LeagueBEhomeBEaway
Standard LiegeBelgium - Jupiler LeagueBEhomeBEaway
Zulte-WaregemBelgium - Jupiler LeagueBEhomeBEaway


SHEET2.TABLE2
DateHomeAway
Tue 21 Jan 20:00AnderlechtKortrijk
Tue 19 Jan 20:00AntwerpKRC Genk
Tue 19 Jan 20:00Beerschot-Wil.KV Mechelen
Tue 19 Jan 20:00BeverenMouscron
Tue 19 Jan 20:00Cercle BruggeOH Leuven
Tue 19 Jan 20:00CharleroiOostende
Tue 20 Jan 20:00Club BruggeSint-Truiden
Tue 19 Jan 20:00EupenStandard Liege
Tue 19 Jan 20:00GentZulte-Waregem
Tue 19 Jan 20:00AC MilanSampdoria
Tue 19 Jan 20:00LazioAS Roma

SHEET3.TABLE("BEhome")
2GFGA
Anderlecht
1​
11​
Antwerp
2​
12​
Beerschot-Wil.
3​
13​
Beveren
4​
14​
Cercle Brugge
5​
15​
Charleroi
6​
16​
Club Brugge
7​
17​
Eupen
8​
17​
Gent
9​
19​
Kortrijk
10​
20​
KRC Genk
11​
21​
KV Mechelen
12​
22​
Mouscron
13​
23​
OH Leuven
14​
24​
Oostende
15​
25​
Sint-Truiden
16​
26​
Standard Liege
17​
27​
Zulte-Waregem
18​
28​

SHEET3.TABLE("BEaway")
2GFGA
Anderlecht
11​
1​
Antwerp
12​
2​
Beerschot-Wil.
13​
3​
Beveren
14​
4​
Cercle Brugge
15​
5​
Charleroi
16​
6​
Club Brugge
17​
7​
Eupen
17​
8​
Gent
19​
9​
Kortrijk
20​
10​
KRC Genk
21​
11​
KV Mechelen
22​
12​
Mouscron
23​
13​
OH Leuven
24​
14​
Oostende
25​
15​
Sint-Truiden
26​
16​
Standard Liege
27​
17​
Zulte-Waregem
28​
18​
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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
Back
Top