Forum Discussion
Pulling exact same data across multiples tables
I've looked around and could not find an answer.
Example i have 4 sets of tables like this
table 1 table 2
| 1 | 20 | 100 | 40 | 1 | 10 | 300 | 50 | |
| 1 | 25 | 100 | 50 | 2 | 12 | 100 | 35 | |
| 1 | 30 | 150 | 70 | 2 | 40 | 275 | 90 | |
| 2 | 33 | 250 | 40 | 3
| 30 | 125 | 45 | |
| 2 | 40 | 275 | 90 | 3 | 27 | 145 | 50 | |
| 2 | 50 | 300 | 100 | 3 | 10 | 235 | 75 | |
| 3 | 60 | 100 | 70 | 4 | 60 | 155 | 35 | |
| 4 | 65 | 150 | 50 | 4 | 70 | 175 | 40 | |
| table 3 | table 4 | |||||||
| 1 | 10 | 100 | 35 | 1 | 5 | 100 | 30 | |
| 2 | 15 | 125 | 45 | 1 | 6 | 125 | 40 | |
| 2 | 33 | 200 | 60 | 2 | 10 | 150 | 20 | |
| 2 | 40 | 275 | 90 | 2 | 40 | 275 | 90 | |
| 3 | 47 | 125 | 45 | 3 | 27 | 100 | 60 | |
| 3 | 60 | 175 | 65 | 3 | 39 | 150 | 70 | |
| 4 | 65 | 150 | 20 | 3 | 45 | 175 | 80 | |
| 4 | 80 | 200 | 30 | 3 | 50 | 300 | 90 |
I need a function that can find the matching rows and populate all of them
so
| 2 | 40 | 275 | 90 |
or any others because each table will have about 8000 rows of data each
Hi jordan1213
If by any chance one of the proposals you got solves the issue it would be smart of you to click on Mark as solution (at the bottom of the corresponding post) to help those who Search (as you looked around)
Thanks
Hi jordan1213
Alternatively with 8k rows/table I would do it by merging tables (Inner Join) with Power Query (many videos exist on the Net)
Given you only have 4 tables you can easily do this with almost no knowledge of PQ, repeating the merges one after the other (3 merges in total). Alternatively the attached workbook does it by iterating the list of tables to merge (added an add. dummy record - 96...99 - that matches):
=AND(COUNTIFS($F$3:$F$10,A3,$G$3:$G$10,B3,$H$3:$H$10,C3,$I$3:$I$10,D3)>0,
COUNTIFS($A$14:$A$21,A3,$B$14:$B$21,B3,$C$14:$C$21,C3,$D$14:$D$21,D3)>0,
COUNTIFS($F$14:$F$21,A3,$G$14:$G$21,B3,$H$14:$H$21,C3,$I$14:$I$21,D3)>0)You can apply AND along with COUNTIFS. The formula returns TRUE (WAHR in german Excel) for the rows of data which occur in all 4 tables. The ranges within COUNTIFS can be adapted as required.
=FILTER(A3:D10,E3:E10)
If you have access to the FILTER function you can apply it to select only the rows that occur in all 4 tables.
=IFERROR(INDEX(A$3:A$10,SMALL(IF($E$3:$E$10=TRUE,ROW($A$3:$A$10)-2),ROW($A1))),"")
If you don't have access to the FILTER function you can use this formula to select only the rows that occur in all 4 tables. The formula is in cell K7 and filled across range K7:N10. The formula must be entered with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
