Count of Consecutive Duplicate Number

[Lasantha]

Dear All,

Kindly look into the attached file. It has some remarks of employees.

Could you please help me to count Consecutive Duplicate Numbers.

Example. Raw 2,

it has duplicate consecutive numbers as "22" and "11", so it should count as 2.

Raw 3

it has 11, 22,11, and 11, so it should count as 4 .

Raw 4

it has 22,22 and 11 , it should be 3

Raw 5

it has 11, so count is 1.

Thank you very much for your time.
Lasantha.

 

[Peter Bartholomew]

The solution depends upon the version of Excel you are using. I can use the dynamic array formula
= SEQUENCE(LEN(remark)-1)
to define a sequence of numbers 'k' for each 'remark'.
Then the count of adjacent matches is given by
= SUM( SIGN( MID(remark, k, 1) = MID(remark, k+1, 1) ) )

If you are using an older version of Excel, you could let 'k' refer to
= ROW(INDIRECT("1:"&LEN(remark)))
whilst 'remark' is the relative reference to
=Sheet1!$B2

 

[bosco_yip]

Or,

In C2, formula copied down :

=SUMPRODUCT(0+(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)-MID(B2&0,ROW(INDIRECT("2:"&LEN(B2)+1)),1)=0))

 

[Lasantha]

The solution depends upon the version of Excel you are using. I can use the dynamic array formula
= SEQUENCE(LEN(remark)-1)
to define a sequence of numbers 'k' for each 'remark'.
Then the count of adjacent matches is given by
= SUM( SIGN( MID(remark, k, 1) = MID(remark, k+1, 1) ) )

If you are using an older version of Excel, you could let 'k' refer to
= ROW(INDIRECT("1:"&LEN(remark)))
whilst 'remark' is the relative reference to
=Sheet1!$B2

Thank you very much.

 

[Lasantha]

Or,

In C2, formula copied down :

=SUMPRODUCT(0+(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)-MID(B2&0,ROW(INDIRECT("2:"&LEN(B2)+1)),1)=0))

View attachment 69957

Thank you very much.