count up the occurences of non continuous value

[koala]

How do I count up the occurences of non continuous value,

I have a large dataset, that I have to check daily, and need to get two separate results for.

1st requirement.

My data always starts in row 7. The amount of rows is variable, often as high as 50000

In column “W” are the values “ON” / “OFF” or blank.

How can I count up the occurrences the word “ON” is there, and only count each group of “ON” as 1 occurrence..

Eg
OFF, ON, OFF, ON, OFF, would count as 2
OFF, ON, ON, OFF, ON, OFF, would also count as 2
OFF, ON, ON, OFF, OFF, ON, ON, OFF, also = 2
OFF, ON, OFF, ON, ON, OFF, ON OFF, would count as 3

Sometimes there will also be empty cells scattered throughout the list as well, these empty cells could be between the ON and wil not end that particular occurence. OFF is the switch between start and end of each occurrence

2nd requirement.

In column “D” is a timestamp, (row 7 being the earliest, row 8 the next etc.). I need to know the total time that the value in column “W” was “ON”.

Eg. The dataset may look like this

D W
6:17:47 OFF
6:17:49 ON
6:17:50 ON
6:17:52
6:17:53 OFF
6:17:54 OFF
6:17:59 ON
6:18:01
6:18:03 ON
6:18:04 ON
6:18:07 OFF
This would sum 12 Seconds -- 4 seconds on first occurrence, (6:17:49 to 6:17:53) and 8 seconds in the second occurrence (6:17:59 to 6:18:07).

As you can see, an empty cell should not have an impact on the result.

I know this sounds complex, but I am sure someone is able to assist.

Thanks in advance
Koala

[Rich_z]

For your first requirement, you would need to count how many times the 'state' of the cell changes.

something like:

Please Login or Register  to view this content.

Embed this in a loop that starts at row 7 down to the last line of your spreadsheet.

As for your second request - I don't have the time at the mo - Sorry!

[davidm]

for your 2nd requirement the following loop will do the sum for you:

For each c in range("W7:W"&[w65536].End(xlUp).row)
if c.value= "ON" Then
s= s +c.offset.(-19,0)
end if
next

[koala]

Thanks Rich_z and davidm,

Unfortunately I dont know a lot about vba and macros, and have to be a pain and ask to you show me how to put the code into a macro and make it work.

Do I need to add something else as well? I tried recording a macro, selecting the cell where I want the value to be put, then placed your code in the macro as well, but when I ran the macro, nothing appears to happen.

Can you please assist further,

Koala

[Rich_z]

Hi Koala,

It's not as simple as it seems.

Unfortunately to actually teach you what you need to know to do this would basically mean writing the stuff ourselves. I hate having to say this, but I work as a contract programmer and I'm answering these questions from work, and I really don't have the time to spend on that as my client would not get his work done!!

What I would suggest though is that if this is becoming part of your job, get your company to either send you on a VBA course or, get them to buy a book such as Wrox's Excell 2000 VBA (Or whatever version of Excel you are using) ISBN 1-861002-54-8. Use the Online help as well in VBA.

I'm really sorry I can't help you in full.

Regards

Rich

[koala]

Thank you Rich,

I had a gut feeling that it would not be simple, otherwise everyone would know how to do it.

I am currently trying to learn VBA and have purchased a book titled "Excel 2002, Power Programming with VBA", by John Walkenbach.

Unfortunately trying to learn by reading books isnt always the best way to learn until you have mastered the basics and can fully understand what the author is trying to tell you.

Anyway thanks for the time you have taken, I am sure if I keep trying I will get something to work to make this task a bit easier.

cheers
Koala