Combine two matrix one under the other

%3CLINGO-SUB%20id%3D%22lingo-sub-3025611%22%20slang%3D%22fr-FR%22%3ECombine%20two%20matrix%20one%20under%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3025611%22%20slang%3D%22fr-FR%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20two%20tables%3A%20%5BGroup_A%5D%2C%20%5BGroup_B%5D.%20I%20want%20to%20put%20all%20the%20data%20of%20each%20other%20in%20one%20table.%20I%20want%20to%20use%20formulas%20because%20each%20table%20might%20grow%20over%20the%20time.%20The%20purpose%20is%20to%20analyse%20the%20data.%20Each%20table%20share%20the%20same%20headlines.%20I%20don't%20want%20to%20use%20VBA%20if%20possible.%20See%20the%20file%20attached%20or%20just%20here%20%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ET_Group_A%3C%2FSTRONG%3E%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2299.86504723346827%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3EGroup%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3EExam1%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3EExam2%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3EExam3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3E%3CP%3EGroup_A%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3EApple%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3E67%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3E33%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3E7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3EGroup_A%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3EWell%20done%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3E54%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3E23%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3EGroup_A%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3ECharlie%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3E94%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3E40%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2230px%22%3E9%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ET_Group_B%3C%2FSTRONG%3E%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2299.86504723346827%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EExam1%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EExam2%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EExam3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup_B%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EDelta%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E77%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E34%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup_B%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EEcho%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E86%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E38%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup_B%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EFoxtrot%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E90%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E39%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup_B%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EGolf%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E45%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E23%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E5%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20this%20result%20%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2299.86504723346827%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EExam1%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EExam2%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EExam3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup_A%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EApple%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E67%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E33%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup_A%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EWell%20done%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E54%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E23%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup_A%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3ECharlie%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E94%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E40%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E9%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup_B%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EDelta%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E77%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E34%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup_B%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EEcho%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E86%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E38%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup_B%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EFoxtrot%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E90%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E39%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2219.973009446693656%25%22%20height%3D%2221%22%3EGroup_B%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3EGolf%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E45%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E23%3C%2FTD%3E%3CTD%20width%3D%2219.973009446693656%25%22%3E5%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3025611%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3025923%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20two%20matrix%20one%20under%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3025923%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1235820%22%20target%3D%22_blank%22%3E%40leolo7%3C%2FA%3E%26nbsp%3BConsider%20using%20Power%20Query.%20Query%20both%20tables%20and%20Append%20them%20into%20a%20third%20that%20you%20load%20back%20to%20Excel.%20Just%20keep%20on%20adding%20data%20to%20the%20tables%2C%20press%20Refresh%20All%20on%20the%20data%20ribbon%20and%20you're%20done.%20Demonstrated%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20new%20to%20PQ%2C%20the%20link%20below%20could%20be%20a%20good%20starting%20point.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChapter%208%20deals%20with%20combining%20and%20appending%20queries.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3028017%22%20slang%3D%22fr-FR%22%3ERe%3A%20Combine%20two%20matrix%20one%20under%20the%20other%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3028017%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20fast%20response%20and%20solution.%20I'm%20not%20familiar%20with%20Power%20Query.%20The%20solution%20is%20probably%20in%20%22Get%20Data..%22.%20however%2C%20I%20don't%20have%20this%20in%20my%20menu.%20I%20have%20the%20latest%20version%20of%20Excel%20for%20Mac%20(16.55)%2C%20Microsoft%20365.%20Any%20setting%20I%20should%20change%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20find%20this%20on%20Microsoft%20web%20site%20(for%20Mac)%20%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22fd483d21-b12d-425b-91d6-a9e0eedc5047.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F331253i4724E91F1EED34D6%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22fd483d21-b12d-425b-91d6-a9e0eedc5047.png%22%20alt%3D%22fd483d21-b12d-425b-91d6-a9e0eedc5047.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20have%20%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture%20d%E2%80%99e%CC%81cran%2C%20le%202021-12-02%20a%CC%80%2010.58.08.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F331256i2FE5D70CDC838AD6%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture%20d%E2%80%99e%CC%81cran%2C%20le%202021-12-02%20a%CC%80%2010.58.08.png%22%20alt%3D%22Screenshot%2C%20the%202021-12-02%20at%2010.58.08.png%22%20%2F%3E%3C%2FSPAN%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

GroupNameExam1Exam2Exam3

Group_A

Apple67337
Group_ABravo54233
Group_ACharlie94409

 

T_Group_B

GroupNameExam1Exam2Exam3
Group_BDelta77347
Group_BEcho86388
Group_BFoxtrot903910
Group_BGolf45235

 

 

I want this result : 

GroupNameExam1Exam2Exam3
Group_AApple67337
Group_ABravo54233
Group_ACharlie94409
Group_BDelta77347
Group_BEcho86388
Group_BFoxtrot903910
Group_BGolf45235

 

Thanks

7 Replies

@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) : 

fd483d21-b12d-425b-91d6-a9e0eedc5047.png

This is what I have : 

Capture d’écran, le 2021-12-02 à 10.58.08.png

 

@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!

Thank you very much. This explain my problem !!!

@leolo7 

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?

@leolo7 

Sorry, I didn't catch. Output is the spill, aka dynamic array.