Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
count up the occurences of non continuous value
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 -- koala ------------------------------------------------------------------------ koala's Profile: http://www.excelforum.com/member.php...o&userid=12953 View this thread: http://www.excelforum.com/showthread...hreadid=386389 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
count up the occurences of non continuous value
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! -- Rich_z ------------------------------------------------------------------------ Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737 View this thread: http://www.excelforum.com/showthread...hreadid=386389 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
count up the occurences of non continuous value
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 -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=386389 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
count up the occurences of non continuous value
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 -- koal ----------------------------------------------------------------------- koala's Profile: http://www.excelforum.com/member.php...fo&userid=1295 View this thread: http://www.excelforum.com/showthread.php?threadid=38638 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
count up the occurences of non continuous value
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 -- Rich_z ------------------------------------------------------------------------ Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737 View this thread: http://www.excelforum.com/showthread...hreadid=386389 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
count up the occurences of non continuous value
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 -- koal ----------------------------------------------------------------------- koala's Profile: http://www.excelforum.com/member.php...fo&userid=1295 View this thread: http://www.excelforum.com/showthread.php?threadid=38638 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count continuous duplicates | Excel Worksheet Functions | |||
Continuous count down | Excel Discussion (Misc queries) | |||
Count Occurences | Excel Discussion (Misc queries) | |||
Count # of Occurences | Excel Discussion (Misc queries) | |||
Count occurences | Excel Programming |