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.

 

[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;

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:00	1.2	1.8
		5.7
16/07/2015 04:33	1.7	4.2
		5.7
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
		9.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>

 

[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 !!