Count continuous duplicates
 

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

Count continuous duplicates
 

In article ,
Excel Curious wrote:

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),LOO KUP(9.99999999999999E+3
07,CHOOSE({1,2},$C$1-ROW(B2)+1,MATCH(TRUE,B2:INDEX(B:B,$C$1)<B2,0)))), B2
),"")

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

Count continuous duplicates
 

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

"Excel Curious" wrote in message
...
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