Transpose and split data adding N number of empty columns after each blank cell (Google Sheets)-CodePudding

On google sheets I'm trying to split a column on every empty cell then transposing the column and adding N number of empty columns (in example N=2) after each split resulting from a blank cell. One difficulty I am having is the number of empty cells vary between each set on the data. Attached is sample of data and desired output

initial data

I tried using a combination of split and transpose but was unable to get desired output.

Desired

Desired output

CodePudding user response：

I updated the formula a little bit, just realized that my latter-added filter also helped for the first one.

=TRANSPOSE(INDEX(
 IFERROR(
  SPLIT(
    TRANSPOSE(SPLIT(
      LAMBDA(mapped,JOIN("╬",FILTER(mapped,mapped<>"")))
        (MAP(A1:A,LAMBDA(x,IF(ROW(x)=1,x,IF(AND(x="",OFFSET(x,-1,0)<>""),REPT("SPLIT",B1 1),x))))),
     "SPLIT",,))
    ,"╬",1,1)
  ,"")
))

I'll try to explain it from the inside to the outside:

MAP(A1:A,LAMBDA(x,IF(ROW(x)=1,x,IF(AND(x="",OFFSET(x,-1,0)<>""),REPT("SPLIT",B1 1),x)))) goes all through the column, when it finds a cell that is blank and its previous one wasn't (that's why OFFSET with -1, it goes "up" one row) then it repeats the word "SPLIT" N times in that cell (you can try pasting only this part to see the effect)

Then i added a LAMBDA function in order not to repeat all this expression when trying to filter. LAMBDA(mapped,JOIN("╬",FILTER(mapped,mapped<>""))) I named "mapped" to the result of the previous operation, FILTER allows you to skip the empty rows (that would be the extra rows you have in A12 or A23:25). And then JOIN with that symbol ╬ (that you can change to another you like). Now you have a loooong string with ╬ between what used to be cells and SPLITSPLITSPLIT when you had the division of categories

TRANSPOSE(SPLIT(....,"SPLIT",,)). With SPLIT now you divide all that long string in one cell per column with each category that looks like this: Birds╬Double Crested╬Great Blue Heron╬ and with TRANSPOSE you make it into this:

Now you SPLIT again with the symbol: SPLIT(.....,"╬",1,1)) Those last 1,1 let's you avoid empty spaces. And the previous INDEX and IFERROR goes all through the "chart" in the previous screenshot row by row

Finally,TRANSPOSE again let you recover the original position of rows and columns, now divided

I hope it is clearer now!

B1 is the place where you can set that "N" amount of columns in between your results. Basically it repeats the word "SPLIT"(it can be anyone you choose, or any symbol, obviously) You can test it

CodePudding user response：

try:

=INDEX(LAMBDA(a, TRIM(TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(
 TRIM(FLATTEN(SPLIT(QUERY(IF(a="", "×", a&""),,9^9), "×"))), 
 "select Col1,'','' where Col1 is not null label '''',''''", ))), ""))))
 (A1:INDEX(A:A, MAX(ROW(A:A)*(A:A<>"")))))