Count Consecutive Repeating Words

Hi all

I need to keep a count of either the word “UP” or “DOWN” in column H and keep a running tally for the word “UP” in Column K and the word “DOWN” in Column T. But there’s consecutive UP’s or DOWN’s in column H and I only want to see the total in Column K, so that’s why I'm thinking I probably need a macro. However, I’m open to any other way of completing the task.

By the way, I was doing this manually and it took me a few hours for 500 entries, but I’ve got to complete multiple workbooks of 5,000 entries. Yikes!

I’ve attached a sample sheet so you can see the desired results. I will always start in cell H2 and end in the last cell in H.

If at all possible, anyone that provides the code, could you please place a couple notes in the macro so I can figure it out. I have the "Excel VBA For Dummies" book and just got the "VBA and Macros: Microsoft Excel 2010" but still haven't read all of it, but would like to understand the commands being used.

Thanks and any assistance is very much appreciated!

Do you explicitly need the values in the first row of the streak ?
(rather than the last which might be a bit quicker to calculate)

Try:

=IF(AND($H2=K$1,$H1<>K$1),MATCH(TRUE,INDEX($H3:$H$36<>K$1,0),0),"")

adjust ranges to suit and copy down and to next column if you have "down" in L1

Here:

=IF(H1="UP","",IF(H2="UP",MATCH("DOWN",H3:$H$36,0),""))

Originally Posted by ExcelQuestFL

The last row in the streak will work just fine.

In which case - given the volume of data perhaps:

K2:
=IF(OR($H2<>K$1,$H2=$H3),"",COUNTIF($H$2:$H2,K$1)-SUM(K$1:K1))
copied down and applied to Col T

If you are going to use the first in the streak, and if you only have UP and DOWN options, then zbor's is superior over mine... but if you have multiple text strings and you want to find streak of one, then mine should work.. or for last in streak, DO's.

Originally Posted by NBVC

if you only have UP and DOWN options, then zbor's is superior over mine

I think zbors will fail for either/or column depending on the last value be it UP/DOWN

well yes... But only in case of one last UP (DOWN, DOWN, DOWN, DOWN, DOWN, DOWN, DOWN, DOWN, DOWN, DOWN, DOWN,UP) other will give 1 or more so error can be ignored

Originally Posted by DonkeyOte

I think zbors will fail for either/or column depending on the last value be it UP/DOWN

I guess mine too

To adjust for that, make the range in the formula one row larger than the database.

i.e.

=IF(AND($H2=K$1,$H1<>K$1),MATCH(TRUE,INDEX($H3:$H$37<>K$1,0),0),"")

copy down and to next column.

Originally Posted by zbor

well yes... But only in case of one last UP (DOWN, DOWN, DOWN, DOWN, DOWN, DOWN, DOWN, DOWN, DOWN, DOWN, DOWN,UP) other will give 1 or more so error can be ignored

If the last value in the column matches the header you will get an error irrespective of streak length - it follows that the last calculation in one of the two columns will fail.

Yes.. I ment in all other cases you'll get correct answer only in this case you get #N/A instead of 1....

But yes, can and should be avoided