Count Consecutive Values
 

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!

Count Consecutive Values
 

Hi,

Put this in b1 and drag down

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

Mike

"The Question guy" wrote:

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!

Count Consecutive Values
 

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...

"Mike H" wrote:

Hi,

Put this in b1 and drag down

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

Mike

"The Question guy" wrote:

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!

Count Consecutive Values
 

Perhaps with 2 helpers..

in B2, 1

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

copy B3 to bottom

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

How does that one do?

"The Question guy" wrote:

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!

Count Consecutive Values
 

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.

Count Consecutive Values
 

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" wrote:

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.

Count Consecutive Values
 

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

I tried them, and it worked perfectly! Thanks!

"Sean Timmons" wrote:

Perhaps with 2 helpers..

in B2, 1

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

copy B3 to bottom

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

How does that one do?

"The Question guy" wrote:

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!

Count Consecutive Values
 

Excellent! :-)

Happy to help!

"The Question guy" wrote:

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

I tried them, and it worked perfectly! Thanks!

"Sean Timmons" wrote:

Perhaps with 2 helpers..

in B2, 1

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

copy B3 to bottom

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

How does that one do?

"The Question guy" wrote:

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!