admin管理员组文章数量:1418621
I have a table in Google Sheets with products (URLs) in one column and a list of comma-separated colors in another column. I want to transform it into a new table where each product is repeated for every individual color, creating a 'product-variant' pair for each combination.
How can I achieve this dynamically, so if the original table changes, the new table updates automatically? I've tried using formulas like SPLIT and ARRAYFORMULA, but I can't get it to work across multiple rows.
Original Table:
| Products | Colors |
|---|---|
| / | Red, Blue, Green, Yellow, Black, White |
| / | Red, Blue, Green, Yellow, Black, White |
| / | Red, Blue, Cyan, Green, Lime Green, Yellow, Magenta, Black, White |
I have a table in Google Sheets with products (URLs) in one column and a list of comma-separated colors in another column. I want to transform it into a new table where each product is repeated for every individual color, creating a 'product-variant' pair for each combination.
How can I achieve this dynamically, so if the original table changes, the new table updates automatically? I've tried using formulas like SPLIT and ARRAYFORMULA, but I can't get it to work across multiple rows.
Original Table:
| Products | Colors |
|---|---|
| https://example/product/item-1/ | Red, Blue, Green, Yellow, Black, White |
| https://example/product/item-2/ | Red, Blue, Green, Yellow, Black, White |
| https://example/product/item-3/ | Red, Blue, Cyan, Green, Lime Green, Yellow, Magenta, Black, White |
Desired Output Table:
| Product | Variant |
|---|---|
| https://example/product/item-1/ | Red |
| https://example/product/item-1/ | Blue |
| https://example/product/item-1/ | Green |
| https://example/product/item-1/ | Yellow |
| https://example/product/item-1/ | Black |
| https://example/product/item-1/ | White |
| https://example/product/item-2/ | Red |
| https://example/product/item-2/ | Blue |
| https://example/product/item-2/ | Green |
| ... | ... |
4 Answers
Reset to default 1Here's another solution:
=ARRAYFORMULA(
QUERY(
SPLIT(
TOCOL(A2:A & "❅" & SPLIT(B2:B, ", ", ), 3),
"❅"
),
"WHERE Col2 IS NOT NULL"
)
)
You may also use this alternative formula, which should work with your desired output:
=WRAPROWS(TOROW(MAP(TOCOL(A1:A,1),
TOCOL(B1:B,1),
LAMBDA(products,
colors,
TOROW(BYROW(TRANSPOSE(SPLIT(colors,",")),
LAMBDA(x,ARRAYFORMULA(TRIM(SPLIT(JOIN("|",products,x),"|")))))))),1),2)
Sample Output:
| Products | Colors |
|---|---|
| https://example/product/item-1/ | Red |
| https://example/product/item-1/ | Blue |
| https://example/product/item-1/ | Green |
| https://example/product/item-1/ | Yellow |
| https://example/product/item-1/ | Black |
| https://example/product/item-1/ | White |
| https://example/product/item-2/ | Red |
| https://example/product/item-2/ | Blue |
| https://example/product/item-2/ | Green |
| https://example/product/item-2/ | Yellow |
| https://example/product/item-2/ | Black |
| https://example/product/item-2/ | White |
| https://example/product/item-3/ | Red |
| https://example/product/item-3/ | Blue |
| https://example/product/item-3/ | Cyan |
| https://example/product/item-3/ | Green |
| https://example/product/item-3/ | Lime Green |
| https://example/product/item-3/ | Yellow |
| https://example/product/item-3/ | Magenta |
| https://example/product/item-3/ | Black |
| https://example/product/item-3/ | White |
References: WRAPROWS function TOCOL function TOROW function
You may try:
=reduce(A1:B1,A2:A,lambda(Δ,Λ,vstack(Δ,if(Λ="",tocol(,1),let(Σ,tocol(split(index(B:B,row(Λ)),", ",)),ifna(hstack(Λ,Σ),Λ))))))
You can use REDUCE to process each URL separately, VSTACK to accumulate the results, SPLIT to get each of the colors of the column, and BYCOL to process each of the colors:
=REDUCE(TOCOL(,1),A2:A,LAMBDA(previous,url, IF(url="",previous,
VSTACK(previous,
TOCOL(BYCOL(SPLIT(OFFSET(url,0,1),", ",0,1),LAMBDA(color,url&color)))))))
本文标签： google sheetsHow to split commaseparated values across multiple rows dynamicallyStack Overflow
版权声明：本文标题：google sheets - How to split comma-separated values across multiple rows dynamically? - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1745296944a2652135.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论