Dec 01 2021 06:41 PM
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
Dec 01 2021 10:04 PM
@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.
Dec 02 2021 08:03 AM
@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 :
Dec 02 2021 08:23 AM
@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!
Dec 02 2021 09:00 AM
Dec 02 2021 11:50 AM
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) ) )
Dec 02 2021 12:46 PM
Dec 04 2021 06:49 AM
Sorry, I didn't catch. Output is the spill, aka dynamic array.