| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
| Count & Sum Consecutive (2x) appearance of Specific Numeric Values | Excel Worksheet Functions | |||
| Return Summed Count of Multiple Consecutive Numeric Values | Excel Worksheet Functions | |||
| Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| Count consecutive repeted values | Excel Discussion (Misc queries) | |||
Linear Mode
