Label the row according to the source

Question

I want to importrange from Spreadsheet OLD with several tabs/sheets into Spreadsheet NEW. What i want to do is to import values from SheetA and Sheet B. First column: vstack(importrange(SheetA!A5:A),importrange(SheetB!B5:B) easy The problem is here. In second column, I would like to do something like if the row imported is from sheet A , then "Sheet A", if the row imported is from sheet B, then "Sheet B".

My failed approaches: 1.

=VSTACK(
  ARRAYFORMULA(IF(IMPORTRANGE("SheetA", "SheetA!B5:B") <> "", "Sheet A", "")),
  ARRAYFORMULA(IF(IMPORTRANGE("SheetB", "SheetB!B5:B") <> "", "Sheet B", ""))
)

this doesn't work, because i assume, arrayformula doesn't work with vstack. Sheet A is there but it did not anything after that. but you can prove me wrong. 2.

=ARRAYFORMULA(
  IF(A2:A<>"", 
    IF(ISNUMBER(MATCH(A2:A, IMPORTRANGE("SheetA", "SheetA!B5:B"), 0)), "Sheet A",
    IF(ISNUMBER(MATCH(A2:A, IMPORTRANGE("Sheet B", "SheetB!B5:B"), 0)), "Sheet B"
  ""))

close enough, but some of B5:B are from two sheets A or B. So, it would only take SheetA even though it is from SheetB.

So, please give your suggestion.

Welcome to StackOverFlow! Would you be able to provide your sample sheet, with your initial output, and also your expected output so that we can further help you. You may use this to provide a markdown table (you may create one with the help of this link ) or an anonymous sample spreadsheet (using this link — Venara
– Venara, Commented Jan 14 at 3:06
So, there may data in either SheetA or SheetB? Better to share few sample data showing your desired output? — Harun24hr
– Harun24hr, Commented Jan 14 at 3:06
oh sorry! old spreadsheet - docs.google.com/spreadsheets/d/… new spreadsheet - docs.google.com/spreadsheets/d/… — cywh
– cywh, Commented Jan 14 at 3:18

Venara · Accepted Answer · 2025-01-14 04:05:03Z

1

You can try this formula using Query:

=QUERY(
  {
    IMPORTRANGE("OLD_SHEET_URL", "SheetA!B2:B"),
    ARRAYFORMULA(IF(IMPORTRANGE("OLD_SHEET_URL", "SheetA!B2:B")<>"", "SheetA", ""))
  ;
    IMPORTRANGE("OLD_SHEET_URL", "SheetB!B2:B"),
    ARRAYFORMULA(IF(IMPORTRANGE("OLD_SHEET_URL", "SheetB!B2:B")<>"", "SheetB", ""))
  },
  "SELECT Col1, Col2 WHERE Col1 IS NOT NULL",
  0
)

Note: Kindly replace OldsheetUrl with the link to your old sheet.

Sample Output:

Stack of food	Source of Sheet
Carrot	SheetA
Apple	SheetA
Cake	SheetA
Pineapple	SheetA
Grape	SheetA
Pear	SheetA
Chicken	SheetA
Jumbo	SheetB
Cake	SheetB
Pie	SheetB
Grape	SheetB
Strawberry	SheetB

edited Jan 14 at 4:05

answered Jan 14 at 3:55

Venara

8312 gold badges4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Venara Jan 14 at 4:13

You're welcome! Feel free to accept my answer (by clicking the accept button on the left) so that other members in the community (who may have the same concerns as you) may know that their problem can also be solved with this answer

z.. · Accepted Answer · 2025-01-14 04:07:30Z

0

You can use REDUCE.

=ARRAYFORMULA(
   REDUCE(
     {"Food", "Sheet"},
     {"SheetA", "SheetB"},
     LAMBDA(result, sheet, LET(
       import, TOCOL(IMPORTRANGE(
         "Old_Spreadsheet_ID",
         sheet & "!B2:B"
       ), 1),
       stack, HSTACK(import, IF(N(import)^0, sheet)),
       VSTACK(result, IF(ISERROR(stack), TOCOL(,1), stack))
     ))
   )
 )

edited Jan 14 at 4:07

answered Jan 14 at 3:42

z..

14k2 gold badges10 silver badges21 bronze badges

Collectives™ on Stack Overflow

Label the row according to the source

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related