Count Consecutive Values

[The Question guy]

Is there a way to count consecutive values in a column? Preferably I'd prefer
to only count strings that are at least 5 values long. For Example:

Column A Column B
JETS
JETS
JETS
JETS
JETS
JETS 6
CARS
CARS
CARS
CARS
JETS
CARS
JETS


In this example Column A is my data values, and Column B is "helper" column
that counts a consecutive string and identifies it at the end of the string
(A6 is the end of the JETS run, so B6 identifies the string as 6 values long).

Any help would be appreciated. Thanks!

 

[Mike H]

Hi,

Put this in b1 and drag down

=IF(COUNTIF($A$1:A1,A1)>5,COUNTIF($A$1:A1,A1),"")

Mike

 

[Sean Timmons]

Thought that'd be good, but it doesn't account for consecutive. Would show 6,
7 and 8 in the below, but thinking the OP only wants 6 then start over at 1
for the next.

Still working on this one myself...

 

[Sean Timmons]

Perhaps with 2 helpers..

in B2, 1

in B3, =if(A3=A2,B2+1,1)

copy B3 to bottom

in C2, =if(AND(B2>5,B3=1),B2,"")

How does that one do?

 

[The Question guy]

The key is, I've got almost 10,000 rows of data and I need to be able to
identify every string (of 5 values or greater) within those 10,000 rows.

In addition, I need to be able to indentify any kind of string. Using my
previous example I could have a string of 8 JETS and then several rows later
have a string of 5 CARS. In that instance I only want column B to return 2
numbers 8 and 5.

 

[Sean Timmons]

Understood, but not sure we can get you there without using another column.
The B column could be hidden, so you'd only see the column with your 8 and 5
(column C in my example). But to put both portions in 1 formula may take lots
of formulating...

 

[The Question guy]

Sorry Sean, I hadn't refreshed so I didn't see your responses yet.

I tried them, and it worked perfectly! Thanks!

 

[Sean Timmons]

Excellent!

Happy to help!

Sorry Sean, I hadn't refreshed so I didn't see your responses yet.

I tried them, and it worked perfectly! Thanks!