Hi everyone,

I have 3 tables with different number of columns where some columns are actually repeated in these 3 tables (as shown in the screenshot above).

Table 1: Col A to Col D

Table 2: Col F to Col H

Table 3: Col J to Col L

I want to merge these 3 tables together to reduce the number of columns in my google sheet. The screenshot below is how I process these 3 tables before merging.

I'm using QUERY to create extra columns for each table so that the number of columns and the position are aligned between 3 tables. After that, I use QUERY again to append the 3 processed tables as shown in the screenshot below:

However, this method is very tedious when I have 10 tables or more. Is there any other easier ways or tricks to use so that I can achieve the same expected output as shown in the 3rd screenshot?

This is my sheet:

https://docs.google.com/spreadsheets/d/1H1gJAhp1RVax2fy8D-uEtFxdjb-zAHutkPFv5WZT_TY/edit#gid=0

Any help will be greatly appreciated!

CodePudding user response：

You would need a really complicated formula to get the desired output which is a combination of multiple formula's

I added a new tab in you Google Sheet called "Solution" with this formula included

=QUERY(ARRAYFORMULA({
IFERROR( ArrayFormula(SPLIT(FLATTEN(Sheet1!B3:D3&"~"&Sheet1!A4:A&"~"&Sheet1!B4:D),"~")),"");
IFERROR( ArrayFormula(SPLIT(FLATTEN(Sheet1!G3:H3&"~"&Sheet1!F4:F&"~"&Sheet1!G4:H),"~")),"");
IFERROR( ArrayFormula(SPLIT(FLATTEN(Sheet1!K3:L3&"~"&Sheet1!J4:J&"~"&Sheet1!K4:L),"~")),"")}), 
"SELECT Col2, SUM(Col3) WHERE Col2 is not null GROUP BY Col2 PIVOT Col1 LABEL Col2 'Student Name' ")

Steps:

1. Unpivot each table =IFERROR( ArrayFormula(SPLIT(FLATTEN(Sheet1!B3:D3&"~"&Sheet1!A4:A6&"~"&Sheet1!B4:D6),"~")),"")
2. Combine all tables into one table: =ARRAYFORMULA({__Unpivot1__; __Unpivot2__, __Unpivot3__})
3. Pivot above data in Step2: =Query(__Step2__, "SELECT Col2, SUM(Col3) GROUP BY Col2 PIVOT Col1 LABEL Col2 'Student Name' ")
4. Add WHERE Col2 is not null in the query to remove any possible blank rows