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:

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:

Desired Output Table:

• google-sheets
• array-formulas

4 Answers 4

Here'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:

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