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!
CodePudding user response：
try:
={QUERY(Sheet1!A:B; "select 'Sheet1',A,B label 'Sheet1'''"; );
QUERY(Sheet2!A:B; "select 'Sheet2',A,B label 'Sheet2'''"; )}
CodePudding user response：
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 != '×'")}
}, "×")
Output:
