2

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

player0
  • 124,011
  • 12
  • 67
  • 124
spcsLrg
  • 340
  • 2
  • 11

3 Answers3

1

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Brilliant! `LAMBDA` is the way to go again! (And I need to learn it more to use it more !). Thank you! :) – spcsLrg Nov 26 '22 at 10:51
1

When I opened it, there was no answer but here it goes all the same for you or another user

It checks each row with the next one and adds a "|" when they differ, then joins all rows with that same symbol and split by that same symbol, resulting in empty rows between different values

=transpose (split(join("|",byrow(A1:A,lambda(each,if(each="","",SI(each=offset(each,1,0),each,each&"|"))))),"|",1,0))
Martín
  • 7,849
  • 2
  • 3
  • 13
  • Thank you! While I've accepted the first answer, it's always good to learn with multiple ways of approaching it. Appreciate it! – spcsLrg Nov 26 '22 at 11:02
  • 1
    Agree with you, I think it's useful to learn different logics and being able to replicate the most suitable to a new scenario in the future. Greetings! – Martín Nov 26 '22 at 11:14
1

Here's another solution:

=index(lambda(a,b,query(flatten(split(
a&if(a=b,,"❆ "),"❆")),"where Col1<>''"))
(filter(A:A,A:A<>""),{filter(A2:A,A2:A<>"");0}))
z''
  • 4,527
  • 2
  • 3
  • 12