• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count last sequence of duplicate values of a given value

imla

imla

Member
Hi, I need help using only formula to count last "cl" sequence before empty cell in:
a,b,ab ,ac, cl, bb, cl, cl, ab , cl, cl, cl, cl, cl
the count is 5
And for this sequence cells:
a, b , ac , cl, cl, cl, ab, ac
the count is 0
 
Hi,

Try the below

MMULT(MATCH({0,1},--($A$1:$A$11="cl")),{-1;1})

See the attached for ref.
 

Attachments

  • Imla_chandoo.xlsx
    12.3 KB · Views: 8
Hi Asheesh ,

The formula is not really checking for an empty cell after a sequence of cl ; thus , if A11 is not blank and contains anything other than cl , the result will be 4 ; according to the original post , the result should be 0.

Narayan
 
Hi @NARAYANK991

Thank you for highlighting

I realized that a while ago and working on the new construction and it is almost ready..shall post the amended one in a while..
 
this is a sample file ...the purpose of this formula is to find a sequence that the count exceed a threshold value, and start filling from the empty cell to reset the count (it does not have to be 0)

thank you for the help, and I'm here for any other clarification of the problem!
 

Attachments

  • cl.xlsx
    9.1 KB · Views: 8
Try the below…attached for your ref.

To be array entered

IF(INDEX(A3:K3,COUNTA(A3:K3))="cl",MMULT(MATCH({0,1},--LOOKUP(11-COLUMN($A$1:$K$1)+1,COLUMN($A$1:$K$1),(A3:INDEX(A3:K3,COUNTA(A3:K3)))="cl"),0),{1;-1}),)

Let me know if you are looking for anything else..

Frankly, I am not pleased with the solution and I have a strong gut feeling that there is a simpler way of doing it. Thinking
 

Attachments

  • Copy of cl.xlsx
    9.5 KB · Views: 6
Hi John ,

Can you please verify your formula with the attached file ?

Rather than use the LOOKUP function , I think you will have to use the MAX function.

Narayan
 

Attachments

  • cl.xlsx
    9.5 KB · Views: 10
Thank you all very much, solutions is exactly what I want (BTW I never used the Frequency function) you saved my day this formula helped me a lot.
 
bines53 said:
Hello friends,

It is also possible without CSE,

=COUNTA(A3:K3)-AGGREGATE(14,6,COLUMN(A3:K3)/(A3:K3<>"cl")*(A3:K3<>""),1)

David
Click to expand...
works and also gives other info in negative value (I may use it in conditional formating!)
thanks
 
Hi to all!

Hi, I need help using only formula to count last "cl" sequence before empty cell
Click to expand...

I think the OP wants the last sequence , not the maximum sequence, which is what makes the formula I posted.
Now, I'm assuming there are no spaces between the data but in the end...

Blessings!
 
Hi All,

Some time in Oct-14 I worked on a similar problem related to Attendance issue. My construct was similar to @John Jairo V .

Considering Data in A1:J1.

=LOOKUP(99^99,FREQUENCY(IF($A1:$J1="cl",COLUMN($A1:$J1)),COLUMN($A1:$J1)*($A1:$J1<>"cl")))

With CSE.

Regards,
 
You must log in or register to reply here.
Back
Top