how to query different columns and stack into new table in google sheet-CodePudding

Hi I have columns like so, where it's auto fill every rows. Where column BCD is from source a, column EFG from source b and HIJ from source c

sheet data

A	B	C	D	E	F	G	H	I	J
1	Date	Name	Cost	Date	Name	Cost	Date	Name	Cost
2	2022-01-02	Alan	5	2022-01-03	James	6	2022-01-02	Timmy	5
3	2022-01-02	Hana	5	2022-01-03	Paul	6	2022-01-02	Jane	5

into summary sheet

A	B	C	D	E
1	Date	Name	Cost	Source
2	2022-01-02	Alan	5	sourceA
3	2022-01-02	Hana	5	sourceA
4	2022-01-03	James	6	sourceB
5	2022-01-03	Paul	6	sourceB
6	2022-01-02	Timmy	5	sourceC
7	2022-01-02	Jane	5	sourceC

How do I achieve this with formula query, stacking it on top one another. Source is using if but then how do you detect last row and used it for the if.

the rows for each source might be different.

CodePudding user response：

Use array notation to combine the ranges, and combine them with either filter() or query() to remove the empty rows.

Filter docs

Query docs

CodePudding user response：

this is array: {} inside of it you can use comma , to put something next to each other or semicolon ; to put something under something else. eg. having:

={1,2;3,4}

will yield:

     A       B
   ------ ------- 
1 |  1   |   2
   ------ ------- 
2 |  3   |   4

in that manner you can do:

={QUERY(B:D);
  QUERY(E:G);
  QUERY(H:J)}

side note: if your locale is non-english then comma , in array is replaced by backslash \