admin管理员组

文章数量:1322346

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.

Share Improve this question edited Jan 14 at 3:57 z.. 13.2k2 gold badges9 silver badges20 bronze badges asked Jan 14 at 3:01 cywhcywh 92 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

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