Get hourly stats from array. Need some help please?

[Dan_R]

Hello,
I was hoping someone would be able to help me out with an excel problem I have.
I have a spreadsheet which contains data in one minute intervals in the following format:

[TABLE="width: 214"]
<tbody>[TR]
[TD][TABLE="width: 371"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]7/16/2015 0:00[/TD]
[TD]2.4[/TD]
[TD]5.3[/TD]
[/TR]
[TR]
[TD]7/16/2015 0:01[/TD]
[TD]2.2[/TD]
[TD]4.1[/TD]
[/TR]
[TR]
[TD]7/16/2015 0:02[/TD]
[TD]2.8[/TD]
[TD]6.2[/TD]
[/TR]
[TR]
[TD]7/16/2015 0:03[/TD]
[TD]2.4[/TD]
[TD]5.4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
etc. for 1400 odd records (24hrs)

What I need to end up with is for each hour, the maximum value in column C with the corresponding value in column B. This data then gets pasted into several other sheets. For example, the above data may result in:
[TABLE="width: 371"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]7/16/2015 1:00[/TD]
[TD]2.8[/TD]
[TD]6.2[/TD]
[/TR]
[TR]
[TD]7/16/2015 2:00[/TD]
[TD]4.1[/TD]
[TD]9.5[/TD]
[/TR]
</tbody>[/TABLE]
etc. for 24 records.

I've put them into an array as follows;

Dim Array_1() As Variant
Dim r, i As Integer

r = WorksheetFunction.CountA(Worksheets(1).Columns(1))
    ReDim Array_1(r, 2)
    
For i = 1 To r
    Array_1(i, 0) = Range("A" & i + 2)
    Array_1(i, 1) = Range("B" & i + 2)
    Array_1(i, 2) = Range("C" & i + 2)
Next

But, I'm not sure what to do from there. Do I have to reDim this into 24 sub arrays to get the data for each hour or can I do it directly from the original array? I think I could work out how to do ReDim this using the row number but also, occasionally, a record will be missed (or sometimes duplicated) so that would displace the records from then on. It would be better to actually look at the times.

I know there not much to go on so far, but I'm hoping there's a simple way to do this and I can't seem to find anything.

Thanks in advance for any help.

 

[oldbrewer]

for, say, the 7th hour, do you want only the max for hours 1 to 7

if not - it is pretty easy

 

[Dan_R]

Hang on...
I think I may have it, with a bit of help from a friend. I'll post what I've done here anyway if it works in case any one else finds it useful...

 

[Dan_R]

Ok, I'm still working on it but think I'm making progress, if a bit slow an long winded. I'd like to hear your thoughts though if you have time?

No, for the first Hour I want stats for 00:00 to 01:00
for 2nd hour from 01:00 to 02:00 etc,
so for the 7th hour only for 06:00 to 07:00

Thanks

Sorry only just saw your post

 

[MickG]

Try this based on your Data (Starting "A1"). Results start "G1"

[COLOR=Navy]Sub[/COLOR] MG19Jul14
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, nRng [COLOR=Navy]As[/COLOR] Range, Rw [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] oMax [COLOR=Navy]As[/COLOR] Double
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 3)
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not nRng [COLOR=Navy]Is[/COLOR] Nothing And Minute(Dn.Value) Mod 60 = 0 [COLOR=Navy]Then[/COLOR]
        c = c + 1
        oMax = Application.Max(nRng)
        Rw = Application.Match(oMax, nRng, 0)
        ray(c, 1) = nRng(Rw, -1): ray(c, 2) = nRng(Rw, 0): ray(c, 3) = nRng(Rw, 1)
        [COLOR=Navy]Set[/COLOR] nRng = Nothing
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]
    If[/COLOR] nRng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]Set[/COLOR] nRng = Dn.Offset(, 2)
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]Set[/COLOR] nRng = Union(nRng, Dn.Offset(, 2))
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
Range("G1").Resize(c, 3) = ray
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[Dan_R]

Thanks,
It looks like a much neater solution than what I was working on, but I get a 'Type Mismatch' error at this line;

    If Not nRng Is Nothing And Minute(Dn.Value) Mod 60 = 0 Then

 

[MickG]

Do your Date/times actually start in "A1" ?????? and are they actual "Dates/Times"

 

[Dan_R]

Ahh, could be because the data starts in A3, not A1, that gives me some results, still not quite right but I'll keep working on it;

[TABLE="width: 288"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]16/07/2015 00:22[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]5.5[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 01:08[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]5.7[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 02:06[/TD]
[TD="align: right"]2.6[/TD]
[TD="align: right"]3.8[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 03:00[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]1.8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5.7[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 04:33[/TD]
[TD="align: right"]1.7[/TD]
[TD="align: right"]4.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5.7[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 05:58[/TD]
[TD="align: right"]2.9[/TD]
[TD="align: right"]5.3[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 06:47[/TD]
[TD="align: right"]2.8[/TD]
[TD="align: right"]8.5[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 07:04[/TD]
[TD="align: right"]2.3[/TD]
[TD="align: right"]8.9[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 08:56[/TD]
[TD="align: right"]2.3[/TD]
[TD="align: right"]9.5[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 09:00[/TD]
[TD="align: right"]2.2[/TD]
[TD="align: right"]9.5[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 10:46[/TD]
[TD="align: right"]2.7[/TD]
[TD="align: right"]8.8[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 11:02[/TD]
[TD="align: right"]3.6[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9.5[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 12:59[/TD]
[TD="align: right"]9.3[/TD]
[TD="align: right"]13.8[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 13:26[/TD]
[TD="align: right"]9.6[/TD]
[TD="align: right"]19.3[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 14:51[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"]18.3[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 15:47[/TD]
[TD="align: right"]10.2[/TD]
[TD="align: right"]20.7[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 16:07[/TD]
[TD="align: right"]10.6[/TD]
[TD="align: right"]22.8[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 17:11[/TD]
[TD="align: right"]9.1[/TD]
[TD="align: right"]18.6[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 18:42[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]22.5[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 19:16[/TD]
[TD="align: right"]9.9[/TD]
[TD="align: right"]30.3[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 20:31[/TD]
[TD="align: right"]7.1[/TD]
[TD="align: right"]17.5[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 21:08[/TD]
[TD="align: right"]7.8[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]16/07/2015 22:09[/TD]
[TD="align: right"]6.4[/TD]
[TD="align: right"]10.6[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dan_R]

I have a few blanks as well in the date/time group which I can't avoid, so I'll try cleaning them out first as well.

 

[MickG]

Just seen you post, Ok !!