[VBA] to count similar occurrances

[StephenUK]

I'm trying to count simultaneous occurrences in a time series to plot (possibly by another criteria)
a) simultaneous activities in 15 minute slot
b) busiest times of day

Context:
I have a production line running continuous 24/7. I receive an output (1 line of data) of setting changes when they occur. I am trying to count the number of things that change in a rolling 15 minute time slot. (i.e. how many other things are going on around the same time that action was carried out) There are >1m lines of data to scan through.

Data is organised:
Machine | "Dial" | Time | "activity"

in a formula I would write it as:
=COUNTIFS([Time],">"&[@Time]-15/(24*60),[Time],"<"&[@Time]+15/(24*60))
and
=COUNTIFS([Time],">"&[@Time]-15/(24*60),[Time],"<"&[@Time]+15/(24*60),[Activity],"ON")

I want the output to look like this:
Machine | "Dial" | Time | "activity" | "No of simultaneous" | "No of simultaneous activity"

I found this which looked close:
http://www.mrexcel.com/forum/excel-...lications]-count-number-occurances-array.html
but couldn't work out how to make it output to the rows (i.e. not the summarised solution but similar to the User's first attempt)

 

[Rijnsent]

Hi Stephen,

wouldn't it be much easier to predefine your 15-minute blocks (24*4 per day, starting with 00:00-00:15) and then count the occurences in those blocks instead of runnning a calculation for every line of data? That does simplify/standardize your analysis process considerably and gives an answer to A & B.

Cheers,

Koen

 

[StephenUK]

Koen - thanks for looking at this.

I originally did it your suggested way, however, it doesn't show what I really need. Your proposal shows the busiest time of day, which is different to the busiest time.

The formulas do what I need when the dataset is about 50k lines as Excel doesn't have a problem with it; but over 750k lines of data it is too slow. Hence trying to find if a VBA solution will speed up the calculation time.

To hopefully make it clearer: I'm looking to chart the frequency of startups, so how often do 2, 3, 4 machines start up together. Together is classed as +/- 15 minutes, so it's a rolling timeframe rather than a static (i.e. not 10:00 to 10:15). if this example data helps make it clearer:

Example Data
Machine | "Dial" | Time | "activity"
1|A|10:11|on
2|Z|10:16|on
3|Z|10:20|off

should result in:
Machine | "Dial" | Time | "activity" | "No of simultaneous" | "No of simultaneous activity"
1 | A | 10:11 | on | 3 | 2
2 | Z | 10:16 | on | 3 | 2
3 | Z | 10:20 | off | 3 | 1

Hopefully an interesting one for you to solve!

 

[Rijnsent]

Hi Stephen,

I've done some basic testing: a COUNTIF formula takes a massive time to calculate, my laptop almost overheats with the 50k dummy values I created. I tried achieving the same with a MATCH formula and that goes way faster. My first speed-tests: 130 seconds for the COUNTIF, 0.3 seconds for the MATCH. Running a loop over the data (no real calculation, just some basic stuff): 0.2 seconds. The "only" condition for the MATCH is to have the data sorted by time. My gut feeling is that the VBA solution will be much more slow then a MATCH formula, so I would go with the following option:

COUNTIF (don't use it, slooooow):
=COUNTIFS([Time];">"&[@Time]-15/(24*60);[Time];"<"&[@Time]+15/(24*60))
MATCH:
=IFERROR(MATCH([@Time]+15/(60*24);[Time];1);0)-IFERROR(MATCH([@Time]-15/(60*24);[Time];1);0)

-> Basically the formula assumes an ordered list and is looking for the "first match".

No of simultaneous is thereby covered, but the "No of simultaneous activity" not, as it spans two columns (Activity & Time). Assuming your time is really a time (or in Excel terms: a number between 0 and 1), it is easy to create a macro that filters the list by "Activity = ON", copy-pastes that to a new sheet and does the calculation there for just the ONs. If you don't want to copy-paste: Add a fomula like: =[@Time]+IF([@Activity]="ON";1000;0) and sort by that column (I named it TimeActivity).

My handywork can be found here (named long_list_speedtest.xlsm):
https://www.dropbox.com/sh/l7ywfwzfk5j20sr/AACXwUGywU-4J-IWwdZY58Ppa?dl=0

And at 1 million lines I'd recommend MS Access or decreasing the amount of data ,

Cheers,

Koen

 

[StephenUK]

Koen, thanks again.

I'll admit it is a smart solution and works much much quicker, but it does rely on the list being sorted and it also doesn't do the trick of picking out the "on" operations. I need to do the same activity with "On" and "Off" and "adjust" and combined so it doesn't work to pull the numbers out into different tables.

I wanted to make the task repeatable so that people could run the results every month and hopefully see the progress - hence the request for VBA. Having said that don't let me stop you from finding a "formula" solution!

once again, many thanks for trying.

 

[StephenUK]

With a little bit of thinking about the code, this is what I have. However, I can't work out the "SIMILAR" bit of the code.
I'm not sure if my "Dim" are wrong as when I try

[COLOR=#574123]If Arr(R, 1) <= Arr(C, 1) +(15/(24*60)) Then[/COLOR]

I get a type mis-match error

Sub CPmacro()

Dim Arr() As Variant
Arr = Range("I1:I10")
Status = Range("H1:H10")
Dim R As Long
Dim C As Long
Dim TimeM As Double, TimeC As Double
For R = 1 To UBound(Arr, 1)
 If Status(R, 1) = "ON" Then
    Counter = 0
    For C = 1 To UBound(Arr, 1)
    If Arr(R, 1) <= Arr(C, 1) Then
    Counter = Counter + 1
    End If
    Next C
    Cells(R, 10).Value = Counter
 End If
Next R
End Sub

 

[Rijnsent]

Hi Stephen,
did you check my file, as it does give a solution for your ON/OFF question... So what I would programatically do:
-get your data in Excel
-format it as a table, sort by timestamp (as is, I'm assuming that's your original order)
-Column E: add a column with =[@Time]+IF([@Activity]="ON";1000;0) for ON/OFF calculations, calculate that formula, then copy-paste values (not to slow the rest of the calculations down later on)
-Column F: =IFERROR(MATCH([@Time]+15/(60*24);[Time];1);0)-IFERROR(MATCH([@Time]-15/(60*24);[Time];1);0) , calculate, copy-paste values
-Sort table by column E (ascending) - TimeActivity
-Column G, add formula: =IFERROR(MATCH(E2+15/(60*24);[TimeActivity];1);0)-IFERROR(MATCH([@TimeActivity]-15/(60*24);[TimeActivity];1);0), calculate that formula, copy-paste values
-sort table by column C (time) and you're done...

Your last solution: I tried running that with 50K lines... It basically means 50.000 x 50.000 rounds and the macro isn't finished after 5 minutes, the MATCH & sorting takes max 1 minute. That might imply that your record set of 750k lines should go to MS Access, otherwise your users have a high chance of their PC complaining about memory shortage etc.

Cheers,

Koen

 

[StephenUK]

Bowing to you and your better solution. After much wasted time, it works just fine doing it your way.

Thanks for helping me see the light!

 

[Rijnsent]

Cool, happy to help, it was also a challenge to me, as it was a first time for me to bump into such slow formulas, so a more creative solution was needed .

Cheers,

Koen