Search code examples
google-sheetslambdagoogle-sheets-formulaflattengoogle-query-language

Processing a list to output in a particular format

I am processing a list to output its items in chunks separated by blank rows as follows. But the result is not working when there are similar items, as shown with the arrows.

enter image description here

The formula I'm using is =query(filter(flatten({if(COUNTIFS($A$1:$A,$A$1:$A,ROW($A$1:$A),"<="&ROW($A$1:$A))=1," ",),A1:A}),flatten({if(COUNTIFS($A$1:$A,$A$1:$A,ROW($A$1:$A),"<="&ROW($A$1:$A))=1," ",),A1:A})<>""),"offset 1",0)

I need some help with it, to get the repeated chunks right too, so that the desired result is following. I've tried tweaking the COUNTIF conditions but am struggling.

Desired result

enter image description here

Solution

  • try:

    =INDEX(LAMBDA(a, QUERY(FLATTEN(SPLIT(QUERY(IFERROR(
 IF(a={""; a},"×"&a,"×​×"&a)),,9^9), "×")), "offset 1", ))
 (A1:INDEX(A:A, MAX(ROW(A:A)*(A:A<>"")))))

    enter image description here