Split comma separated data into multiple rows-CodePudding

The data below pulls from another sheet in the same workbook. This data below will update over time and have more rows (so it is not static).

How can I "split" this data so each shipment ID has its own row?

For the first row, this would look like:

The implemented logic would need to work for each row in the first image which means row 2 (with Shipment IDs F,G,H,I) would be split into four separate rows. The logic would need to be dynamic to be able to split more rows as they are pulled in from the other sheet.

Ideally, I do not want to use a script.

CodePudding user response：

try this out:

={A1:D1;INDEX(QUERY(SPLIT(FLATTEN(C2:C&"|"&D2:D&"|"&MAP(A2:A,B2:B,LAMBDA(ax,bx,IFNA(SPLIT(ax,",")&"|"&SPLIT(bx,","))))),"|",0,0),"SELECT Col3,Col4,Col1,Col2 Where Col3!=''"))}

CodePudding user response：

if A and B column are independent use:

=INDEX(LAMBDA(x, QUERY(IFERROR(SPLIT(FLATTEN(TRANSPOSE(FLATTEN(ROW(x)&"×"&
 SPLIT(x, ",")))&"×"&FLATTEN(ROW(x)&"×"&SPLIT(OFFSET(x,,1), ",")&"×"&
 OFFSET(x,,2)&"×"&OFFSET(x,,3))), "×")), 
 "select Col2,Col4,Col5,Col6 
  where Col6 is not null 
    and Col1=Col3 
  order by Col2", ))
 (A11:INDEX(A:A, MAX(ROW(A:A)*(A:A<>"")))))