How to rearrange my data? split and transpose

1
votes

I have raw data with several pieces of information in one cell separated by a special symbol. I would like to be able to rearrange the data into rows but keeping the same ID, name, first name and age.

Please see the image below:

Example

I tried with filters, queries, splits and transpose but I haven't been able to make it work.

The link to my google sheet is: https://docs.google.com/spreadsheets/d/1ECBAJ9jpqmohKAHl-Kpqp1cK5dGW3JiDC38WsWkuzSQ/edit#gid=352138315

1
arraysgoogle-sheetsgoogle-sheets-formulatransposearray-formulas

1 Answers

1
votes

try:

=ARRAYFORMULA({SPLIT(TRANSPOSE(SPLIT(CONCATENATE(REPT(
 IF(A3:A9<>"", "♥"&A3:A9&"♦"&B3:B9&"♦"&C3:C9&"♦"&D3:D9, ), 
 LEN(REGEXREPLACE(""&E3:E9, "[^]+", )))), "♥")), "♦"),
 TRANSPOSE(SPLIT(TEXTJOIN("", 1, E3:E9), "")),
 TRANSPOSE(SPLIT(TEXTJOIN("", 1, F3:F9), "")),
 TRANSPOSE(SPLIT(TEXTJOIN("", 1, G3:G9), "")),
 TRANSPOSE(SPLIT(TEXTJOIN("", 1, H3:H9), ""))})

0