Counting the number of consecutive cells/rows that have the same entry

[Chantelle]

Hi,
I have tried using the count functions in excell to count the number
of cells that are in consecutive order that have the same value, but I
can not get my head around it. I have rows of data with fixed entries
say A B C D and E and i want to put into the column next to the
entries a formula /vba programme that counts the the numbers above and
below if they = the number in the cell and only until there is a
change in numbers e.g

Column A Column B
A 2
A 2
B 1
C 2
C 2
B 1
D 2
D
C
C
C
D
E

 

[Chantelle]

Hi,
I have tried using the count functions in excell to count the number
of cells that are in consecutive order that have the same value, but I
can not get my head around it. I have rows of data with fixed entries
say A B C D and E and i want to put into the column next to the
entries a formula /vba programme that counts the the numbers above and
below if they = the number in the cell and only until there is a
change in numbers e.g

Column A Column B
A 2
A 2
B 1
C 2
C 2
B 1
D 2

Does anyone have any idea how to solve this problem in VBA?

 

[Ron Rosenfeld]

Hi,
I have tried using the count functions in excell to count the number
of cells that are in consecutive order that have the same value, but I
can not get my head around it. I have rows of data with fixed entries
say A B C D and E and i want to put into the column next to the
entries a formula /vba programme that counts the the numbers above and
below if they = the number in the cell and only until there is a
change in numbers e.g

Column A Column B
A 2
A 2
B 1
C 2
C 2
B 1
D 2
D
C
C
C
D
E

Take a look at the Data/Subtotal wizard. Perhaps it will easily do what you
want.



--ron

 

[Stephen Bullen]

Hi Chantelle,

I have tried using the count functions in excell to count the number
of cells that are in consecutive order that have the same value, but I
can not get my head around it. I have rows of data with fixed entries
say A B C D and E and i want to put into the column next to the
entries a formula /vba programme that counts the the numbers above and
below if they = the number in the cell and only until there is a
change in numbers e.g

You can achieve this using worksheet functions, but it takes two columns:

A B C
1 Col1 Col2 Col3
2 A =IF(A2=A1,B1+1,1) =IF(A2=A3,C3,B2)
3 A ...copy down... ...copy down...
4 B

Column C then contains the numbers you want.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk