Merge multiple tabs in Google Sheets and add a column for where the data came from

Question

I have a spreadsheet which contains multiple tabs with similar layouts. I want to use a formula to merge these into a single tab which has a new column naming the tab it came from.

Example

Tab: Area A

| Item | Status      |
|------|-------------|
| Foo  | Blocked     |
| Bar  | In Progress |

Tab: Area B

| Item   | Status    |
|--------|-----------|
| Foobar | Completed |

Tab: Merged

| Area | Item   | Status      |
|------|--------|-------------|
| A    | Foo    | Blocked     |
| A    | Bar    | In Progress |
| B    | Foobar | Completed   |

Merging without new column

I can merge the data without the additional column, using this formula:

=ARRAYFORMULA(SORT({'Area A'!A2:B; 'Area B'!A2:B}))

Which looks like this:

|--------|-------------|
| Item   | Status      |
|--------|-------------|
| Foo    | Blocked     |
| Bar    | In Progress |
| Foobar | Completed   |

Adding the Area column

What's missing from the above formula is the addition of the area column. This would be possible by cross-referencing the item in every tab using a vlookup and labelling it. But that wouldn't be very efficient and some updates are already slow to re-calculate in this document. I expect this to have approx. 40 tabs with 10,000 rows in total to merge.

Eg:

=IFS(NOT(ISERROR(VLOOKUP(B2,'Area A'!A$2:A,1,FALSE))), "A", NOT(ISERROR(VLOOKUP(B2,'Area B'!A$2:A,1,FALSE))), "B")

Is there a better way to do this?

I'd like something like this, but it doesn't work as the constant I'm adding doesn't match the number of rows it needs to be:

=ARRAYFORMULA(SORT({{"A",'Area A'!A2:B}; {"B", 'Area B'!A2:B}}))

player0 · Accepted Answer · 2020-08-20T10:58:29.420

you can borrow empty column and do:

=ARRAYFORMULA(SORT({{'Area A'!X2:X&"A", 'Area A'!A2:B}; 
                    {'Area B'!X2:X&"B", 'Area B'!A2:B}}))

or you can add it to first column and then split it:

=ARRAYFORMULA(QUERY(SORT({SPLIT(
 {"A♦"&'Area A'!A2:A; 
  "B♦"&'Area B'!A2:A}, "♦"), 
 {'Area A'!B2:B; 
  'Area B'!B2:B}}), "where Col2 is not null", 0))

see: https://stackoverflow.com/a/63496191/5632629

Merge multiple tabs in Google Sheets and add a column for where the data came from

Example

Merging without new column

Adding the Area column

Is there a better way to do this?

1 Answers1