CODEWITHSUNDEEP
google-sheets
N.O.Davis
N.O.Davis

Reputation: 513

How can I remove consecutive duplicates from a range in Google Sheets?

How do I remove consecutive duplicates from a range in Google Sheets?

For example, let's say in cells A2:A7 the text is Dog, Cat, Cat, Fish, Fish, Dog. I would want to output Dog, Cat, Fish, Dog.

I have a formula that can do that =ARRAYFORMULA(TEXTJOIN(">",TRUE,LET(rng, T2:T, IF(rng=OFFSET(rng, -1,0),,rng)))) BUT when I attempt to use it with BYROW, it no longer works.

LINKED HERE is a sample sheet with my specific case. I'm trying to get routes

Column N shows my current output but it still shows consecutive duplicates. Column P shows my desired output. Column R is my first attempt using one single formula. However, it is throwing the error "argument must be a range." However, when I break the formula into parts (see Effort 2 in columns T and U), I can get the desired result. I want to accomplish this in a single formula.

Upvotes: 0

Views: 59

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 30122

Here's a generalized approach which you may adapt accordingly:

=map(J2:J,lambda(Σ,if(Σ="",,join(">",let(Λ,tocol(filter(C:D,A:A=Σ,H:H="Yes")),tocol(map(vstack(Λ,),vstack(,Λ),lambda(f,q,if(f=q,,f))),1))))))

enter image description here

Upvotes: 3

Related Questions