How to find group data?

E

Estua25

New Member
Joined
Jun 6, 2016
Messages
2
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:

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

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!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Try this for result starting "J1", Based on column "E" = 100
Code: 
[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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
SUMIFS
Replies
1
Views
301
Rijnsent
Rijnsent
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
231
Raj1979
R
SRMPURCHASE
  • Question Question
#NUM! ERROR
Replies
2
Views
226
SRMPURCHASE
SRMPURCHASE
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
455
Sundance_Kid
S
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
310
JMWh1t3
J

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top