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:


Code:
--------------------

Select Case Current_State
Case "ON"
If New_State <> Current_State then
Current_State = New_State
End IF
Case "OFF"
If New_State = "ON" then
On_Count = On_Count + 1
Current_State = New_State
End If
End Select

--------------------


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
pain and ask to you show me how to put the code into a macro and mak
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 you
code in the macro as well, but when I ran the macro, nothing appears t
happen.

Can you please assist further,

Koal

 

[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 everyon
would know how to do it.

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

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

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

cheers
Koal