I have two columns in a Google sheet: an ID column with a single value per cell and Color column with an arbitrary number of comma separated values per cell. I'd like to expand the Color column so each value is in its own cell, while retaining their associated IDs. This is illustrated in the attached image, and in this worksheet.
I'm looking for a script or formulas to perform this operation on an array with ~30,000 rows. My clumsy solution (below and in second tab of worksheet) gives an error above about 1000 rows, as the length of a string made by join() is limited to 50,000 characters:
Create a list of repeated ID's with the same number of elements as as the list of colors in column C
=rept(A4&",",COUNTA(split(B4,", ",false)))
Concatenate each column then split each column:
=transpose(split(join(",",C4:C),",",false))
=transpose(split(join(", ",B4:B),", ",false))