count last number appear

[sierra spiegel]

How do I count the number of calls it takes a number to repeat? For example: 1 2 3 4 5 6 7 8 1
so it takes 8 calls for number 1 to repeat.

thanks

EggHeadCafe - Software Developer Portal of Choice
Design Pattern Interview Questions Part 3
http://www.eggheadcafe.com/tutorial...84-70948cdfa6b2/design-pattern-interview.aspx

 

[JLatham]

Assuming that your list of numbers to test for repeats is in column A, and
that you enter the number to check for in cell C1 (your 1 in this case), then
this is the basis for the formula to do the counting:
=MATCH(C1,INDIRECT("A" & MATCH(C1,A:A,0)+1 & ":A65536"),0)-MATCH(C1,A:A,0)+1

But there are a couple of potential errors that would return a #NA! error:
the number you enter in C1 is not in the list at all, or it is not repeated.
This formula will catch those and give you a clear indication of "what's
wrong":
=IF(ISNA(MATCH(C1,A:A,0)),"Not In List",IF(ISNA(MATCH(C1,INDIRECT("A" &
MATCH(C1,A:A,0)+1 & ":A65536"),0)-MATCH(C1,A:A,0)+1),"Not
Repeated",MATCH(C1,INDIRECT("A" & MATCH(C1,A:A,0)+1 &
":A65536"),0)-MATCH(C1,A:A,0)+1))

I used 65536 as the last row number to check when looking for the repeated
number, but any number large enough to encompass the entire list would do as
long as it is not greater than the maximum number of rows in your version of
Excel.