Search code examples
arraysgoogle-sheetstransposearray-formulasgoogle-sheets-query

Split and Join with special symbol

I need your help, I created a spreadsheet and I managed to split data separated by a special symbol, but now that I put a lot of raw data my formula is not working and I don't understand why.

This is the link: https://docs.google.com/spreadsheets/d/1uHhyZkyasGkwVVbMheQqXAAOVoZHH_Al6SOgk-JUxbE/edit?usp=sharing

enter image description here

My formula is this one:

=iferror(ARRAYFORMULA({SPLIT(TRANSPOSE(SPLIT(CONCATENATE(REPT(
 IF('Data rearranged'!A3:A<>"", "♥"&'Data rearranged'!A3:A&"♦"&'Data rearranged'!B3:B&"♦"&'Data rearranged'!C3:C&"♦"&'Data rearranged'!D3:D&"♦"&'Data rearranged'!E3:E, ), 
 LEN(REGEXREPLACE(""&'Data rearranged'!F3:F, "[^]+", )))), "♥")), "♦"),
 TRANSPOSE(SPLIT(TEXTJOIN("", 1, 'Data rearranged'!F3:F), "")),
 TRANSPOSE(SPLIT(TEXTJOIN("", 1, 'Data rearranged'!G3:G), "")),
 TRANSPOSE(SPLIT(TEXTJOIN("", 1, 'Data rearranged'!H3:H), "")),
 TRANSPOSE(SPLIT(TEXTJOIN("", 1, 'Data rearranged'!I3:I), ""))}),"")

Everything else is working fine, except the data that is supposed to be shown in the Courses area. I guess there is something with the special symbol but I can't figure out what it is.

I would appreciate any help on this :)

Solution

  • the issue is 12 missing rows in column G by having 3 instead of 0 at the beginning of an array:

    0

    this will output 188 rows instead of 200 and causing the error