Count continuous duplicates

[Excel Curious]

I'm trying write a formula to count the duplicates in a column where they
appear contiguously (adjacent to one another). Another way of putting this is
to count the number of times a number appears continuously in a column before
it changes to another number. I have Excel 2007.

Example:
ColumnA ColumnB ColumnC(answer)
0001 30 2
0001 30
0001 70 1
0001 30 2
0001 30

Your help is greatly appreciated

 

[Domenic]

I'm trying write a formula to count the duplicates in a column where they
appear contiguously (adjacent to one another). Another way of putting this is
to count the number of times a number appears continuously in a column before
it changes to another number. I have Excel 2007.

Example:
ColumnA ColumnB ColumnC(answer)
0001 30 2
0001 30
0001 70 1
0001 30 2
0001 30

Your help is greatly appreciated

Let's assume that Column B contains the data, starting at Row 2. Then
try...

C1:

=MATCH(9.99999999999999E+307,B:B)

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(B2<>B1,COUNTIF(B2:INDEX(B2:INDEX(B:B,$C$1),LOOKUP(9.99999999999999E+3
07,CHOOSE({1,2},$C$1-ROW(B2)+1,MATCH(TRUE,B2:INDEX(B:B,$C$1)<>B2,0)))),B2
),"")

 

[Ashish Mathur]

Hi,

Try this array formula (Ctrl+Shift+Enter) in cell C5 and copy down. I have
assumed that the numbers are in range B5:B9. Please ensure that you leave a
couple of blank rows below the table blank

=IF(B5=B4,"",MATCH(FALSE,($B6:$B9=B5),0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com