Count consecutive rows with same value

[Roxorion]

I have a column with 12682 rows, with values between 1 and 31.
I want to know how many times each number shows up consecutively.

Here is the first 18 cells in the column:

6
12
14
14
17
17
13
13
16
9
7
7
5
6
6
1
1
1

<tbody>
</tbody>

So for this I want to know that
there are
3 consecutive 1's
2 consecutive 6's
2 consecutive 7's
2 consecutive 9's
2 consecutive 14's
2 consecutive 17's
2 consecutive 13's

and if there was another 6 at the top it would report
4 rows of consecutive 6's. Since there would be two groups of 2 consecutive rows of 6's

 

[MDCurry]

Try this
Assume your data exists in range A2:A19
In cell B2 enter formula:

=IF(OR(A2=A1,A2=A3),1,"")

and copy down to B19. This will return a 1 whenever a consecutive numeric sequence exists.

To get your totals (at the bottom):
In cell A21 enter 1, In Cell A22 enter =A21+1 and copy down 30 rows
In Cell B21 enter the following formula:

=SUMIFS($B$2:$B$19,$A$2:$A$19,A21,$B$2:$B$19,1)

Copy down 30 rows

In Cell C21 enter formula:

=B21&" consecutive "&A21&"'s"

Copy down 30 rows

 

[VBA Geek]

If your long list of numbers is along column A, and you place the unique values down column D starting in D1,

you enter in E1:
=SUMPRODUCT(IF(FREQUENCY(IF($A$1:$A$18=D1,ROW($A$1:$A$18)),IF($A$1:$A$18<>D1,ROW($A$1:$A$18)))>1,FREQUENCY(IF($A$1:$A$18=D1,ROW($A$1:$A$18)),IF($A$1:$A$18<>D1,ROW($A$1:$A$18)))))
confirming with Control Shift Enter and drag down