Search code examples
google-sheetsgoogle-sheets-formula

Google Sheets: Text result of JOIN is longer than the limit of 50000 characters

I have a Google Sheet that has a few thousand rows and two columns in this format:

Email No of tickets
[email protected] 1
[email protected] 7

I have a formula that repeats each email address on its own row n times (where n=No of tickets). So in the example above [email protected] would e printed on 1 row, [email protected] would be printed on 7 rows...

The formula that works is:

=ARRAYFORMULA(TRANSPOSE(SPLIT(JOIN("", ARRAYFORMULA(REPT(A2:A&"♦", B2:B))), "♦")))

However, if there are too many rows of data in the input table then I get the error: "Text result of JOIN is longer than the limit of 50000 characters."

Are there any ways around this limitation?

Solution

  • You may try:

    =let(Δ,index(match(,0/(A:A<>""))),
     tocol(map(A2:index(A:A,Δ),B2:index(B:B,Δ),lambda(Σ,Λ,if(or(Σ="",Λ=""),,wraprows(Σ,Λ,Σ)))),1))