Google Sheets, Array Formula to merge 2 Sheets into one, and append a new column to specify which sheet the data came from

Question

I am trying to merge Sheet1 & Sheet2 into Sheet3. On Sheet3 I would like Column A to display which Sheet the data came from.

Sheet1 (Source #1)

A	B
John Doe	123 Street

Sheet2 (Source #2)

A	B
Jane Smith	999 Street

Sheet3 - Expected results using the formulas below.

A	B	C
Sheet1	John Doe	123 Street
Sheet2	Jane Smith	999 Street

I have tried the following formulas.

=ARRAYFORMULA(
  {
    {"Sheet1",FILTER('Sheet1'!A1:B,'Sheet1'!A1:A<>"")};
    {"Sheet2",FILTER('Sheet2'!A1:B,'Sheet1'!A1:A<>"")}
  }
)

=ARRAYFORMULA(
  {
    {"Sheet1",QUERY('Sheet1'!A1:B,"SELECT * WHERE A is not null")};
    {"Sheet2",QUERY('Sheet2'!A1:B,"SELECT * WHERE A is not null")}
  }
)

Both give the error: In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

What am I doing wrong? Thanks!

user_5632629 · Answer

Try:

={QUERY(Sheet1!A:B; "select 'Sheet1',A,B label 'Sheet1'''"; ); 
  QUERY(Sheet2!A:B; "select 'Sheet2',A,B label 'Sheet2'''"; )}

user_16118686 · Answer

Since you can't append the sheet name to multiple rows, you need to get creative.

What this does is combine the columns into one and append the sheet name per row together with the same delimiter used inside the query. Then append both results then proceed to split them using the said delimiter.

Formula:

=ARRAYFORMULA(SPLIT({
{"Sheet1×"&QUERY({Sheet1!A1:A&"×"&Sheet1!B1:B}, "where Col1 != '×'")};
{"Sheet2×"&QUERY({Sheet2!A1:A&"×"&Sheet2!B1:B}, "where Col1 != '×'")}
}, "×")