google-sheetsgoogle-sheets-formulaformulaspreadsheet

How to unnest data from one column to fit the entire dataset - Google Sheets

I'm trying to unnest data in one column separated by " | ". The rest of the columns should "follow" the unnested logic from that column.

I've made a sample spreadsheet to summarise my problem.

Found a similar problem here but could not replicate it for my use-case. Also I do have in my original spreadsheet 50 columns so I'm not sure this is the right approach.

Thanks in advance for any help.

edit: Added a blank cell in the table

Solution

  • Try this Solution

    The unnest data where the "Date" column contains multiple date values separated by " | ", and each row in the table should be expanded to multiple rows, one for each date.

    It also ensures that the "name", "last name", and "email" columns follow the unnested logic for each date.

    Note : Kindly update the Column for the Date time format after pasting the formula.

    Updated Formula used

    =BYROW(SORT(TOCOL(BYROW(B2:E6, LAMBDA(r, let(x, CHOOSECOLS(r,1,2,3), y,CHOOSECOLS(r,4), z, if(y <> "", SPLIT(y,"|"),""), TOROW(BYROW(TOCOL(z),LAMBDA(c, JOIN("|",x,c))))))))), LAMBDA(m, IFERROR(SPLIT(m, "|"),"")))

    Sample Output

    Sample1

    References