Code to identify the start of an event and then report that time - multiple columns

[ScottL]

Hi,
Can anyone help me out here, it's been a while since I used VBA and the old grey matter is fighting my will to remember!
Firstly I will describe what I have and then what I am trying to do. I have the bare bones using excel formulas (example attached) but it is massively resource hungry.
I have a table of extracted data for a large fleet of plant machinery, and it identifies the current condition by code numbers 1-11. It is extracted at a 5 minute interval with the DTG down the lefthand side of the Dbase and a column for each machine.
What I need to do/happen is look at each column for (lets say) code number 11, and then identify the start of each period (reading from top to bottom) that 11 is showing and report the corresponding date/time in a new table. The new table would effectively be a condensed column of all the start date/times for each machine.

[Bob Phillips]

No example attached, but have you tried pivot tables?

[ScottL]

11599
No example attached, but have you tried pivot tables?
Hi, xld
I hadn't thought that pivot tables could do this, can you please have a look at the attachment and see what you think?
TVM

ScottL

[Bob Phillips]

I would mate if there were an attachment. Maybe you are falling foul of the fact that you need x posts before you can add an attachment. Can you put ii on a file sharing site and give us a link?

[ScottL]

Hi,
Ok, lets try Dropbox!
Aaaargh after five posts apparently!
I'll PM you if thats allowed!

[Bob Phillips]

Scott, that file has a load of #REFs where the formulas should be, and in the spinner celllink. What is Aggregated data, which is the code column, B?

[ScottL]

HI,
Sorry I know what has happened, I had to slim it down when I tried to get it down to an acceptable file size for the upload the forum wouldn't let me do!
Yes the code is in column B of of the sheet "Raw Data".
I will repair and upload, then PM you when its there.
Cheers!

[Bob Phillips]

Sorry for the delay Scott, I have been in Dublin for a couple of days.

See what you think of this.

[ScottL]

HI xld,
Thanks, wow thats definately the going the right way, I would need the plant serial number at the top of the results columns also.
Would this work for all 88 plant or would the code need a tweak?

[Bob Phillips]

What's the plant serial number Scott? As I don't know what it is I can't say if any tweaks are needed for all 88.

[ScottL]

Hi,
Its the alpha numeric at the top of the column for the whole fleet it would be A-K, 1-8

[Bob Phillips]

If it is at the top of the column, that is just one is it not. Where does 88 plant come into it?

Generally, to incorporate in a pivot, it is better to have it in each row.

[ScottL]

Hi,
The data that I extract comes in a csv format, date time in column A and then a seperate column for each plant.
So it would look like this:
Date/Time A1 A2 A3 A4 A5 A6 A7 A8 B1
01/03/2014 08:05 3 0 0 1 3 11 9 3 0
01/03/2014 08:10 3 3 0 0 2 11 0 3 0


...and so on. So it gets to be a huge amount of data real quick.
If we could replicate the functionality in your workbook for 88 columns, that would be the kind of thing I am aiming for.
So there would be one huge raw data sheet and then a result sheet listing one column for each plant the date/time of the start of any given/selected code event.

[Bob Phillips]

Okay, I understand it now. Would you show all plant numbers in a single pivot such as column headings, or would the plant number be selected from a slicer as is the code?

[ScottL]

Hi,
I would always look at the whole fleet, but other may want to examine one at a time. So maybe the whole on one sheet and a slicer on another would be really handy.

[Paul_Hossler]

I've been following this thread to see how it resolves, so if you could you attach a small sample of the data and the PT or macro or other solution, I'd appreciate it

Thanks

[ScottL]

Hi Paul,
I'm cool with that, but its xld's work so its up to him.
If he's happy, I think I can post the finalised "full fat" version on here. But it would be quite short thanks to the file limit.
I could Dropbox it and PM a link it if that would work better, again if xld is happy for that to happen.

cheers

Scott

[Bob Phillips]

I've been following this thread to see how it resolves, so if you could you attach a small sample of the data and the PT or macro or other solution, I'd appreciate it


Paul, I know it requires another tweak, but I did post a working solution in thread #8.

[Bob Phillips]

Scott, I have just tried to do this for 4 plant numbers, and it got very messy. With 88 I can't see it being usable (there will be a lot of formulae, and the pivot will be unmanageable). The fact that you have a cross-tab report (date/time rows, plant number columns) is not conducive to good pivotting. We could unpivot it, but that would create 300K rows (assuming you only have 3,500 rows originally), and I am not sure how my formula would need tweaking to give your answer.

If you were to opt for just a single selection plant number on the Raw Data sheet, I think it would be manageable. This would essentially be what we have now.

The other option that occurs to me but I have explored deeply is to use Power Pivot.

[Paul_Hossler]

Paul, I know it requires another tweak, but I did post a working solution in thread #8.

Sorry, that seems to be the 'After'. I meant the 'Before' with the original data

[Bob Phillips]

Paul, that file had the Raw Data worksheet, then an intermediary calculation sheet, then a final results sheet. Not sure what that would show you, but is that what you want to see? Scott should be able to post now, he has enough posts, or I could post it if Scott agrees.

[ScottL]

Hi xld,
Yeah its the magnitude thats the killer!
I could drop the resolution to every 10 (max 4650 rows in a month) minutes maybe 15 (max 2976 rows in a month) at a push if we had no option.
Can we try the single selection plant number option and I will show it to a few people?
In the mean time I will install power Pivot (I suspect it is worth having in the tool box?).
Thanks for your help on this so far, as you know its massively resource hungry the way I was doing it!

[ScottL]

Hi Paul,
Here it is, bit clunky but essentially achieves what I am after, but only for a small data sample in this attachment.11620
It's also massively resource hungry and takes forever to run when loading a full months worth of data. :banghead:
Thats running a 2.6 i5 CPU with 8Gb of RAM.

Enjoy!

[snb]

If you avoid formulae and use VBA for the calculations the performance will improve drastically.

To me your posted workbook isn't informative enough on what you try to accomplish.

I used:


Sub M_snb()
sn = Sheets("Raw data").Cells(4, 1).CurrentRegion

With CreateObject("scripting.dictionary")
For j = 2 To UBound(sn)
.Item(sn(j, 2)) = .Item(sn(j, 2)) & "," & sn(j, 1)
Next

For Each it In .keys
sp = Split(it & .Item(it), ",")
Sheet4.Cells(1, Columns.Count).End(xlToLeft).Offset(, 2).Resize(UBound(sp) + 1) = Application.Transpose(sp)
Next
End With
End Sub

[Bob Phillips]

Scott, do you have a full version with all of the plant numbers, it will enable me to test it better.

[ScottL]

Do you want a whole months worth?

[Bob Phillips]

Yeah, why not?

[Paul_Hossler]

Paul, that file had the Raw Data worksheet, then an intermediary calculation sheet, then a final results sheet. Not sure what that would show you, but is that what you want to see? Scott should be able to post now, he has enough posts, or I could post it if Scott agrees.

Sorry, but I must be missing something

I thought the raw data was captured in a 2D array (DateTimes x Plants)

In #8 attachment I can only see 2 worksheets 'RawData' and 'results'

'RawData' appears to be the data converted into a 1D list with the 5 min True/False to faciliate pivoting


:dunno:dunno

[ScottL]

Hi,
Check your PMs!

[ScottL]

#8 is xld pivot table solution, have a look a #23!

br

Scott

[Bob Phillips]

I have put a copy up in my DropBox (https://dl.dropboxusercontent.com/u/4911716/VBAX%20-%2049510%20-%20Code%20analysis%20Full.xlsm).

You select the plant letter and number separately on the Results sheet, which triggers a recxalculate and pivot refresh. I have added some VBA to maqnage this.

Selecting codes to change the pivot is fast enough, but changing the plant number is a tad slow, but hopefully acceptable.

[snb]

another approach in the attachment

[snb]

If you are only interested in the start time each day you can use the amended attachment

[snb]

If a machine can be started several times a day, you'd better use (bonus: processing time reduced to 50% of previous version)

[ScottL]

Hi All,
I've just been pinged to go do a remote site visit, I'll be back online tuesday!
Cheers have a good weekend!

br

Scott

[snb]

The second choice (1 to 11) is dependent on the existence of data for that particular machine.
That dependency has been introduced in this fourth version.

[ScottL]

Hi snb,
Thanks for this, I had a play and it is good at the sorting but it seems to leave the last refresh of dates in place if you select another plant and there is nothing to report?!
Can you get it to clear the listing on a new select somehow?

regards

Scott


If a machine can be started several times a day, you'd better use (bonus: processing time reduced to 50% of previous version)

[snb]

Did you test version 004 ?

Yes, I can clear the listing; but I think you ought to be able to introduce that too.

[ScottL]

Hi,
I've just tried it and excel is reporting unreadable content. :dunno
Can you check and re-upload please?

regards
Scott

[snb]

see the attachment

[ScottL]

Hi xld and snb,
Both answers work really well and each have thier pluses.
I'm going to show them around a few potential users and try to get some feed back.