[FIXED] How to append 3 tables with different number of columns in google sheet?

Issue

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!

Solution

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

Answered By - Gangula

Answer Checked By - Senaida (PHPFixing Volunteer)