Cartesian / Cross Join in Google Sheets for comma separated values

Question

I'm in the process of building an inventory of products for a webstore. The parent products contain a comma separated list of attributes that are available. If it were a tshirt, it would show xs,s,m,l,xl with colours in a list in an other field b,r,g,y (blue, red, green, yellow).

To generate the child inventory, we need to create a cartesian join in Google Sheets of 6 fields, 5 of them have comma separated lists which need to be split and then joined together again within the cartesian join. Some rows don't have values in certain fields so the formula would have to account for that.

I have tried this method and variations of it without success. I can get it to work for 1 row but it falls down as soon as I use a whole column.

Generate all possible combinations for Columns in Google SpreadSheets

I have included an example sheet with test values. I have also included an example of an expected result for each row.

https://docs.google.com/spreadsheets/d/1pi1zjJuiWRJ2iPZo2Ve4dAekyVnGq5Tc0z3rgev_Ikw/edit?usp=sharing

I'd like to try doing this without the use of Google Scripts.

Answer 1

=ARRAYFORMULA(A2&TRANSPOSE(REGEXREPLACE(
SPLIT(FLATTEN(
SPLIT(FLATTEN(TRANSPOSE(SPLIT(B2,","))&IF(C2="",,"-"&SPLIT(C2,","))&"♦︎"),"♦︎")
&"-"&SPLIT(TEXTJOIN(",",TRUE,D2:F2),",")&"♦︎"),"♦︎")
,"-$",)))

Answer 2

see:

=ARRAYFORMULA(QUERY(SUBSTITUTE(TRIM(FLATTEN(QUERY(TRANSPOSE(SUBSTITUTE(QUERY(SPLIT(QUERY(UNIQUE(FLATTEN(FLATTEN(FLATTEN(FLATTEN(FLATTEN(
 FILTER(ROW(A2:A)&"×"&A2:A, A2:A<>IF(,,))&
 SPLIT(TEXTJOIN(",", 1, IF(INDIRECT("A2:A"&COUNTA(A2:A)+ROW(A2)-1)=IF(,,),,IF(B2:B=IF(,,), "×"&ROW(B2:B)&"×♦", "×"&ROW(B2:B)&"×-"&SPLIT(B2:B, ",")))), ","))&
 SPLIT(TEXTJOIN(",", 1, IF(INDIRECT("A2:A"&COUNTA(A2:A)+ROW(A2)-1)=IF(,,),,IF(C2:C=IF(,,), "×"&ROW(C2:C)&"×♦", "×"&ROW(C2:C)&"×-"&SPLIT(C2:C, ",")))), ","))&
 SPLIT(TEXTJOIN(",", 1, IF(INDIRECT("A2:A"&COUNTA(A2:A)+ROW(A2)-1)=IF(,,),,IF(D2:D=IF(,,), "×"&ROW(D2:D)&"×♦", "×"&ROW(D2:D)&"×-"&SPLIT(D2:D, ",")))), ","))&
 SPLIT(TEXTJOIN(",", 1, IF(INDIRECT("A2:A"&COUNTA(A2:A)+ROW(A2)-1)=IF(,,),,IF(E2:E=IF(,,), "×"&ROW(E2:E)&"×♦", "×"&ROW(E2:E)&"×-"&SPLIT(E2:E, ",")))), ","))&
 SPLIT(TEXTJOIN(",", 1, IF(INDIRECT("A2:A"&COUNTA(A2:A)+ROW(A2)-1)=IF(,,),,IF(F2:F=IF(,,), "×"&ROW(F2:F)&"×♦", "×"&ROW(F2:F)&"×-"&SPLIT(F2:F, ",")))), ","))), 
 "where Col1 contains '♦'"), "×"), 
 "select Col2,Col4,Col6,Col8,Col10,Col12  
  where Col1=Col3 and Col3=Col5 and Col5=Col7 and Col7=Col9 and Col9=Col11"), "♦", IF(,,))),,999^99))), " ", IF(,,)), 
 "where not Col1 contains '--'"))

demo spreadsheet

Answer 3

try:

=ARRAYFORMULA(SUBSTITUTE(QUERY(FLATTEN(FLATTEN(
 QUERY(FLATTEN(IF(IFERROR(SPLIT(B2:B, ","))=IF(,,),,
 A2:A&SPLIT(B2:B, ","))), "where Col1 is not null")&
 IFERROR(SPLIT(QUERY(FLATTEN(IF(IFERROR(SPLIT(B2:B, ","))=IF(,,),,
 IF(C2:C=IF(,,), IF(E2:E=IF(,,), CHAR(32),
 SUBSTITUTE("-"&E2:E, ",", ",-")), SUBSTITUTE("-"&C2:C, ",", ",-"))))&"",
 "where Col1 is not null limit "&COLUMNS(SPLIT(TEXTJOIN(",", 1, B2:B), ","))), ",")))&
 IF(IF(,,)=IFERROR(SPLIT(FLATTEN(
 IF(IF(,,)=IFERROR(SPLIT(QUERY(FLATTEN(IF(IFERROR(SPLIT(B2:B, ","))=IF(,,),,
 IF(C2:C=IF(,,), IF(E2:E=IF(,,), CHAR(32), E2:E), C2:C)))&"",
 "where Col1 is not null limit "&COLUMNS(SPLIT(TEXTJOIN(",", 1, B2:B), ","))), ",")),, 
 QUERY(FLATTEN(IF(IFERROR(SPLIT(B2:B, ","))=IF(,,),,
 IF(D2:D=IF(,,), IF(F2:F=IF(,,), CHAR(32), F2:F), D2:D)))&"",
 "where Col1 is not null limit "&COLUMNS(SPLIT(TEXTJOIN(",", 1, B2:B), ","))))), ",")), 0/0, 
 SPLIT(FLATTEN(IF(IF(,,)=IFERROR(SPLIT(QUERY(FLATTEN(IF(IFERROR(SPLIT(B2:B, ","))=IF(,,),,
 IF(C2:C=IF(,,), IF(E2:E=IF(,,), CHAR(32), E2:E), C2:C)))&"",
 "where Col1 is not null limit "&COLUMNS(SPLIT(TEXTJOIN(",", 1, B2:B), ","))), ",")),, 
 QUERY(FLATTEN(IF(IFERROR(SPLIT(B2:B, ","))=IF(,,),,
 IF(D2:D=IF(,,), IF(F2:F=IF(,,), CHAR(32),
 SUBSTITUTE("-"&F2:F, ",", ",-")), SUBSTITUTE("-"&D2:D, ",", ",-"))))&"",
 "where Col1 is not null limit "&COLUMNS(SPLIT(TEXTJOIN(",", 1, B2:B), ","))))), ","))),
 "where Col1 contains '-'"), CHAR(32), ))

demo spreadsheet