admin管理员组文章数量:1323723
I have data related to Product Name, Tool Name, and ID Tool with the following data example:
Product Name | Tool Name | ID Tool (Standard) | ID Tool (Alternative 1) | ID Tool (Alternative 2) |
---|---|---|---|---|
Product A | Tool A1 | 11-A1 | ||
Product A | Tool A2 | 12-A2-01 | 12-A2-02 | 12-A2-03 |
Product A | Tool A3 | 13-A3 | ||
Product B | Tool B1 | 21-B1-01 | 21-B1-02 | |
Product B | Tool B2 | 22-B2 | ||
Product C | Tool C1 |
I have data related to Product Name, Tool Name, and ID Tool with the following data example:
Product Name | Tool Name | ID Tool (Standard) | ID Tool (Alternative 1) | ID Tool (Alternative 2) |
---|---|---|---|---|
Product A | Tool A1 | 11-A1 | ||
Product A | Tool A2 | 12-A2-01 | 12-A2-02 | 12-A2-03 |
Product A | Tool A3 | 13-A3 | ||
Product B | Tool B1 | 21-B1-01 | 21-B1-02 | |
Product B | Tool B2 | 22-B2 | ||
Product C | Tool C1 |
I want to clean it up into the expected output as follows:
For the output, I have tried the formula:
=LET(
data; C3:E11;
row; ROW(C3:E11)-ROW(C3:C11)+1;
column; COLUMN(C3:E11)-COLUMN(C3:C11)+1;
sorted_data; FLATTEN(data);
label; FLATTEN(IF(COLUMN(C3:E11)-COLUMN(C3:C11)=0; "Standard"; "Alternative"));
result; IFERROR(FILTER(HSTACK(sorted_data; label); LEN(sorted_data)>0); HSTACK(""; ""));
result
)
but the tool type information doesn't appear and I'm still confused about how to enter the product and tool name because it keeps giving an error. Any suggestions for improving the formula?
Here's the test sheet link: https://docs.google/spreadsheets/d/11caWeL-PPyioovetrUf6rcy6iTb8vVuHgftAWzffpSM/edit?usp=sharing
Share Improve this question edited Jan 12 at 10:20 z.. 13.2k2 gold badges9 silver badges20 bronze badges asked Jan 12 at 7:04 AnnaAnna 711 silver badge7 bronze badges 2- Please do not upload images of code/data/errors. – TheMaster Commented Jan 12 at 13:44
- Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Jan 12 at 13:45
3 Answers
Reset to default 2You can use:
=ARRAYFORMULA(REDUCE(
{"Product Name"\ "Tool Name"\ "ID Tool"\ "ID Tool Type"};
SEQUENCE(COUNTA(A3:A));
LAMBDA(a; i; LET(
prod; INDEX(A3:A; i);
tool; INDEX(B3:B; i);
ids; INDEX(C3:E; i);
VSTACK(
a;
IFERROR(
SPLIT(
TOCOL(
prod & "❅" & tool & "❅" & TOROW(ids; 1) & "❅" &
REGEXEXTRACT(FILTER(C2:E2; ids <> ""); "\((.+)\)"));
"❅";;
);
{INDEX(A3:B; i)\ ""\ ""}
)
)))))
You may try:
=reduce(tocol(;1);A3:index(A:A;match(;0/(A:A<>"")));lambda(a;c;ifna(vstack(a;let(Λ;index(C:E;row(c));Σ;transpose(ifna(filter(vstack(Λ;regexextract(C2:E2;"\((.+)\)"));Λ<>"")));
hstack(chooserows(index(A:B;row(c));sequence(rows(Σ);1;1;0));Σ))))))
Give a try to the following formula. See you file harun24hr sheet.
=VSTACK(QUERY(INDEX(SPLIT(UNIQUE(FLATTEN(INDEX(A3:A8&"|"&B3:B8&"|"&IF(C3:E8<>"";C3:E8&"|"®EXEXTRACT(C2:E2;"\((.+)\)");""))));"|"));"where Col3 is not null";0);QUERY(A3:D8;"Where C is null";0))
Output from above formula:
Product Name | Tool Name | ID Tool | ID Tool Type |
---|---|---|---|
Product A | Tool A1 | 11-A1 | Standard |
Product A | Tool A2 | 12-A2-01 | Standard |
Product A | Tool A2 | 12-A2-02 | Alternative 1 |
Product A | Tool A2 | 12-A2-03 | Alternative 2 |
Product A | Tool A3 | 13-A3 | Standard |
Product B | Tool B1 | 21-B1-01 | Standard |
Product B | Tool B1 | 21-B1-02 | Alternative 1 |
Product B | Tool B2 | 22-B2 | Standard |
Product C | Tool C1 |
本文标签: google sheetsStandardize IDs from Multiple Columns into One ColumnStack Overflow
版权声明:本文标题:google sheets - Standardize IDs from Multiple Columns into One Column - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1742120936a2421691.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论