Combine data across multiple sheets while keeping columns but ignoring blanks

Question

I have three sheets in a workbook where people enter data (text values) in different columns, with different row lengths.

For example:

Sheet 1

Group 1	Group 2	Group 3
Apple	Apple	Apple
Orange	Orange	Banana
	Banana	Peach
	Pear

Sheet 2

Group 1	Group 2	Group 3
Onion	Onion	Onion
Tomato	Tomato	Leek
Leek		Garlic
		Potato

I'm looking to combine this data into a single sheet, displayed as such:

Group 1	Group 2	Group 3
Apple	Apple	Apple
Orange	Orange	Banana
Onion	Banana	Peach
Tomato	Pear	Onion
Leek	Onion	Leek
	Tomato	Garlic
		Potato

I've tried this formula:

=QUERY({Sheet1!A3:G;Sheet2!A3:G;Sheet3!A3:G},"select * where Col1<>'' or Col2<>'' or Col3<>''",0)

But it adds in blanks for as many as the longest column is on each sheet, like so:

Group 1
Apple
Orange
Onion
Tomato
Leek

Is there anything I can change to have it just list the items per column in the order queried, skipping blank cells as opposed to rows? I found lots of guidance in other questions about consolidating into a single column, but I want to keep the columns separated and consolidate rows instead.

Answer 1

You have to use multiple queries, one for each column. Even after that, we can't stack the arrays horizontally using {,} because arrays are jagged(jagged-arrays). We can use SORT each column to give a appearance of removing blanks, as blanks are sorted to the bottom. Then stack them back. To automate this process, we can use REDUCE to loop:

=REDUCE(
  ,
  SEQUENCE(COLUMNS(A:C)),
  LAMBDA(a,c,
    LAMBDA(
      s,
      IF(a="",s,{a,s})
    )(SORT(INDEX({Sheet1!A2:C;Sheet2!A2:C},0,c)))
  )
)

This removes the original order though

Answer 2

use:

=INDEX(TRANSPOSE(SPLIT(FLATTEN(QUERY(
 IF({Sheet1!A2:C; Sheet2!A2:C}="",,"​"&{Sheet1!A2:C; Sheet2!A2:C}),,9^9)), "​")))