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

3 Answers 3

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

1
  • 1
    Brilliant! LAMBDA is the way to go again! (And I need to learn it more to use it more !). Thank you! :)
    – spcsLrg
    Commented Nov 26, 2022 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))
2
  • Thank you! While I've accepted the first answer, it's always good to learn with multiple ways of approaching it. Appreciate it!
    – spcsLrg
    Commented Nov 26, 2022 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
    Commented Nov 26, 2022 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}))

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.