[VBA] to count similar occurrances

StephenUK

New Member
Joined
Jun 28, 2015
Messages
5
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)
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 28, 2015
Messages
5
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

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 28, 2015
Messages
5

ADVERTISEMENT

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

New Member
Joined
Jun 28, 2015
Messages
5
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
Code:
[COLOR=#574123]If Arr(R, 1) <= Arr(C, 1) +(15/(24*60)) Then[/COLOR]
I get a type mis-match error

Code:
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

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 28, 2015
Messages
5
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

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top