admin管理员组文章数量:1322510
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
2 Answers
Reset to default 1You 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
版权声明:本文标题:google sheets - Label the row according to the source - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1742114710a2421413.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论