google sheets - Label the row according to the source - Stack Overflow

admin管理员组

文章数量:1323007

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.

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.

• google-sheets
• lambda
• google-sheets-formula
• reduce
• array-formulas

Share Improve this question Follow edited Jan 14 at 3:57 z.. 13.2k22 gold badges99 silver badges2020 bronze badges asked Jan 14 at 3:01 cywhcywh 922 bronze badges 3

• 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 – Alma_Matters 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 Commented Jan 14 at 3:06
• oh sorry! old spreadsheet - docs.google/spreadsheets/d/… new spreadsheet - docs.google/spreadsheets/d/… – cywh Commented Jan 14 at 3:18

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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

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))
     ))
   )
 )

本文标签： google sheetsLabel the row according to the sourceStack Overflow