How to find group data?

[Estua25]

Hello,

I'm new here, and I hope someone can help me.

I have an analysis equipment that measure the HP (horse power) consumpted by an electric motor every 20 seconds during 3-7 days. So, I have a between 13,000 and 30,000 points in one column.

The motor have 4 stages (lets use a 100hp motor):

• No-load: consumes aproximately 15hp.
• Starting or disconnecting: goes from 30hp to 100hp or viceversa.
• Load: Consumes 100hp.

For example, we have this data:

Row Date & Time BHP Status 1 05/20/2016 12:20:12 16.4573 No-load 2 05/20/2016 12:20:32 15.0055 No-load 3 05/20/2016 12:20:52 19.3591 No-load 4 05/20/2016 12:21:12 21.2345 No-load 5 05/20/2016 12:21:32 100 Load 6 05/20/2016 12:21:52 100 Load 7 05/20/2016 12:22:12 100 Load 8 05/20/2016 12:22:32 100 Load 9 05/20/2016 12:22:52 100 Load 10 05/20/2016 12:23:12 100 Load 11 05/20/2016 12:23:32 100 Load 12 05/20/2016 12:23:52 63.5054 Starting or disconnecting 13 05/20/2016 12:24:12 39.3763 Starting or disconnecting 14 05/20/2016 12:24:32 31.117 No-load 15 05/20/2016 12:24:52 32.2743 No-load 16 05/20/2016 12:25:12 100 No-load 17 05/20/2016 12:25:32 100 No-load 18 05/20/2016 12:25:52 100 No-load 19 05/20/2016 12:26:12 100 No-load 20 05/20/2016 12:26:32 77.8646 Starting or disconnecting 21 05/20/2016 12:26:52 40.1166 Starting or disconnecting 22 05/20/2016 12:27:12 26.6382 Starting or disconnecting 23 05/20/2016 12:27:32 21.5217 No-load 24 05/20/2016 12:27:52 26.8641 No-load 25 05/20/2016 12:28:12 31.7573 No-load 26 05/20/2016 12:28:32 100 No-load 27 05/20/2016 12:28:52 100 No-load <colgroup><col span="2"><col span="2"><col span="2"><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I need to find lap-timeS when the equipment is Loading. In other words, I need to find, and count the lenght, of each 100 group. For example, in the data showed before, the groups would be:
Row 5 to 11 - 7 points.
Row 16 to 19 - 4 points
Row 26 to 27 - 2 points.

Thanks!

 

[Estua25]

I made a mistake, every time the BHP is on 100, the status should be "Load".

 

[MickG]

Try this for result starting "J1", Based on column "E" = 100

[COLOR="Navy"]Sub[/COLOR] MG06Jun08
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))
Columns("E:E").Replace What:="100", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
[COLOR="Navy"]Set[/COLOR] nRng = Rng.SpecialCells(xlCellTypeBlanks)
ReDim Ray(1 To nRng.Areas.Count, 1 To 2)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng.Areas
        c = c + 1
        Sp = Split(Dn.Address, ":")
        Ray(c, 1) = "Rows " & Range(Sp(0)).Row & " to " & Range(Sp(1)).Row
        Ray(c, 2) = Dn.Count
[COLOR="Navy"]Next[/COLOR] Dn
Range("J1").Resize(c, 2) = Ray
    Columns("E:E").Replace What:="", Replacement:="100", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick