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:

A B C 7/16/2015 0:00 2.4 5.3 7/16/2015 0:01 2.2 4.1 7/16/2015 0:02 2.8 6.2 7/16/2015 0:03 2.4 5.4 <tbody> </tbody>

<tbody>
</tbody>

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:

A	B	C
7/16/2015 1:00	2.8	6.2
7/16/2015 2:00	4.1	9.5

<tbody>
</tbody>

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.

 

[Dan_R]

Sorry, still can't do it,
Here's what I have using your code

Dim Rng As Range, Dn As Range, nRng As Range, Rw As Long
Dim c As Long, oMax As Double

'get rid of blank rows
   Columns("A:A").Select
   Selection.SpecialCells(xlCellTypeBlanks).Select
   Selection.EntireRow.Delete

Set Rng = Range(Range("A3"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 3)
For Each Dn In Rng
    If Not nRng Is Nothing And Minute(Dn.Value) Mod 60 = 0 Then
        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)
        Set nRng = Nothing
    End If


    If nRng Is Nothing Then
        Set nRng = Dn.Offset(, 2)
    Else
        Set nRng = Union(nRng, Dn.Offset(, 2))
    End If
Next Dn
Range("G1").Resize(c, 3) = ray

I'm not very good at this so I don't fuly understand our code, but these are the results I get, starting in G1;

16/07/2015 00:22	2.4	5.5
16/07/2015 01:08	2.5	5.7
16/07/2015 02:06	2.6	3.8
16/07/2015 03:40	1.6	3.3
16/07/2015 04:33	1.7	4.2
16/07/2015 05:58	2.9	5.3
16/07/2015 06:47	2.8	8.5
16/07/2015 07:04	2.3	8.9
16/07/2015 08:56	2.3	9.5
16/07/2015 09:00	2.2	9.5
16/07/2015 10:46	2.7	8.8
16/07/2015 11:02	3.6	7.5
16/07/2015 12:59	9.3	13.8
16/07/2015 13:26	9.6	19.3
16/07/2015 14:51	7.5	18.3
16/07/2015 15:47	10.2	20.7
16/07/2015 16:07	10.6	22.8
16/07/2015 17:11	9.1	18.6
16/07/2015 18:42	10	22.5
16/07/2015 19:16	9.9	30.3
16/07/2015 20:31	7.1	17.5
16/07/2015 21:08	7.8	17
16/07/2015 22:09	6.4	10.6

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Any suggestions?

 

[MickG]

Here's an alternative to allow for Blank rows in Data.
Your Data assumed to start "A3", Results Start "G1".

[COLOR="Navy"]Sub[/COLOR] MG19Jul44
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Temp [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A3"), 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 Dn.Value = "" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not Rng(1) = Dn.Value And Minute(Dn.Value) Mod 60 = 0 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        ray(c, 1) = Temp: ray(c, 2) = Temp.Offset(, 1).Value: ray(c, 3) = Temp.Offset(, 2).Value
        [COLOR="Navy"]Set[/COLOR] Temp = Nothing
    [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] Temp [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] Temp = Dn
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, 2) > Temp.Offset(, 2) [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] Temp = Dn
    [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]

Ahh, thank you Mick, got it working, I am a bit slow after all...
The original version you sent me does work when I set it to look at data starting in A3, just not quite as I was expecting. You' ve asked it to record the actual time of the max, I was expecting the max in the hour i.e. data in G would be for 01:00, 02:00 etc. That's fine though, I can use that anyway.
There was one other problem, the data only went up to 23:59 so I didn't get the last hour and I only 23 rows, but I added a extra row on the end using a DateAdd with a blank record of 00:00 for the following day and now it works fine.

Thanks so much for helping me out with that, it would have taken me ages to do it the way I was trying.

 

[MickG]

You're welcome