Forum Discussion
Combine two matrix one under the other
Hi,
I have two tables : [Group_A], [Group_B]. I want to put all the data of each other in one table. I want to use formulas because each table might grow over the time. The purpose is to analyse the data. Each table share the same headlines. I don't want to use VBA if possible. See the file attached or just here ;
T_Group_A
| Group | Name | Exam1 | Exam2 | Exam3 |
Group_A | Apple | 67 | 33 | 7 |
| Group_A | Bravo | 54 | 23 | 3 |
| Group_A | Charlie | 94 | 40 | 9 |
T_Group_B
| Group | Name | Exam1 | Exam2 | Exam3 |
| Group_B | Delta | 77 | 34 | 7 |
| Group_B | Echo | 86 | 38 | 8 |
| Group_B | Foxtrot | 90 | 39 | 10 |
| Group_B | Golf | 45 | 23 | 5 |
I want this result :
| Group | Name | Exam1 | Exam2 | Exam3 |
| Group_A | Apple | 67 | 33 | 7 |
| Group_A | Bravo | 54 | 23 | 3 |
| Group_A | Charlie | 94 | 40 | 9 |
| Group_B | Delta | 77 | 34 | 7 |
| Group_B | Echo | 86 | 38 | 8 |
| Group_B | Foxtrot | 90 | 39 | 10 |
| Group_B | Golf | 45 | 23 | 5 |
Thanks
7 Replies
If your Excel supports dynamic arrays that could be
=LET( rA, ROWS(T_Result_A), rB, ROWS(T_Result_B), clmns, COLUMNS(T_Result_A), k, SEQUENCE(rA+rB, clmns), i, INT( (k-1)/clmns)+1, j, MOD(k-1, 5) +1, IF( i <= rA, INDEX(T_Result_A, i, j), INDEX(T_Result_B, i-rA, j) ) )- Hi,
Wow, I like that, very simple. Is it possible that the output will a dynamic array? Or can we transform this in a Dynamic array? If yes, can we define Headers?Sorry, I didn't catch. Output is the spill, aka dynamic array.
leolo7 Consider using Power Query. Query both tables and Append them into a third that you load back to Excel. Just keep on adding data to the tables, press Refresh All on the data ribbon and you're done. Demonstrated in the attached file.
If you are new to PQ, the link below could be a good starting point.
https://exceloffthegrid.com/power-query-introduction/
Chapter 8 deals with combining and appending queries.
Riny_van_Eekelen Thanks for your fast response and solution. I'm not familiar with Power Query. The solution is probably in "Get Data..". however, I don't have this in my menu. I have the latest version of Excel for Mac (16.55), Microsoft 365. Any setting I should change?
Thanks!
I find this on Microsoft web site (for Mac) :
This is what I have :
leolo7 Oh my! You're on a Mac. Excel for Mac doesn't support Power Query in the way I used it. Then you'll need VBA which isn't really my thing. Sorry!
