VBA to extract cell data based on If ... And ... And ...

U

UseLessFuel

New Member
Joined
Dec 22, 2012
Messages
37
Hello. I am dealing with large variable-length time-series datasets of domestic heating system sensor data. I need to extract specific cells data based on the content of three cells. Only two columns of data (L and N) are of interest in the attached HTML, although the raw data extends from columns A to U. I have added column W to show what I would like final result to appear in column N.

ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
DateTimeOutdoor Set roomRoomSet room (Zone2)Room (Zone2)Set flow FlowReturnSet tankTankDeliveredWhDefrData 30
Above uses
Sum (Col:Col) only

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] "]ConsumedWh[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] "]UnitMode[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Set - Z1[/TD]
[TD="align: center"]Set - Z2[/TD]
[TD="align: center"]Set - Flow[/TD]
[TD="align: center"]Flow - return[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] "]UnitMode[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Total Mode 0 Consumed Wh [/TD]
[TD="align: center"][/TD]
[TD="align: center"]First Mode 0 after Mode 2 (Wh con)[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01/10/2017 07:27[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]47[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]90[/TD]
[TD="align: center"] 1st zero: [/TD]
[TD="align: center"]80[/TD]
[TD="align: center"]89%[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01/10/2017 07:28[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]47[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]NEED LAST [/TD]
[TD="align: center"]2nd zero:[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6%[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01/10/2017 07:29[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]ROW FOR [/TD]
[TD="align: center"] 3rd zero: [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1%[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]01/10/2017 07:30[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]47[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"]ABOVE![/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]01/10/2017 07:31[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]10[/TD]
[TD="align: right"]34[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]01/10/2017 07:32[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]01/10/2017 07:33[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]01/10/2017 07:34[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]01/10/2017 07:35[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]01/10/2017 07:36[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]9[/TD]
[TD="align: right"]37[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]01/10/2017 07:37[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]01/10/2017 07:38[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]01/10/2017 07:39[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]01/10/2017 07:40[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]01/10/2017 07:41[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]8[/TD]
[TD="align: right"]38[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]01/10/2017 07:42[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]01/10/2017 07:43[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]01/10/2017 07:44[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]47[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]01/10/2017 07:45[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]12[/TD]
[TD="align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]01/10/2017 07:46[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]7[/TD]
[TD="align: right"]39[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]01/10/2017 07:47[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]01/10/2017 07:48[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]01/10/2017 07:49[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]27.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]01/10/2017 07:50[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]28.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]13[/TD]
[TD="align: right"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-2.5[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]01/10/2017 07:51[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]5[/TD]
[TD="align: right"]38[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]01/10/2017 07:52[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]01/10/2017 07:53[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]01/10/2017 07:54[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]01/10/2017 07:55[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]01/10/2017 07:56[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]01/10/2017 07:57[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]01/10/2017 07:58[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]27.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]12[/TD]
[TD="align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]01/10/2017 07:59[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]5[/TD]
[TD="align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]01/10/2017 08:00[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]01/10/2017 08:00[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]01/10/2017 08:01[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]27.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]01/10/2017 08:02[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]11[/TD]
[TD="align: right"]33[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1.5[/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Sheet1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Y2[/TH]
[TD="align: left"]=SUMIF(N2:N38,"=0",L2:L38)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AA2[/TH]
[TD="align: left"]=SUMIF(W:W,"=3",L:L)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AA3[/TH]
[TD="align: left"]=SUMIF(W:W,"=4",L:L)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AA4[/TH]
[TD="align: left"]=SUMIF(W:W,"=5",L:L)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Y7[/TH]
[TD="align: left"]=SUMIFS(L:L,W:W,"=0",W:W,"=3",W:W,"=4",W:W,"=5")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

(Please ignore the Y7 formula above. Still getting used to the HTMLMaker!)

I will try to explain:
In column N, if a UnitMode = 2 is immediately followed by a UnitMode =0 AND if (in Column L) the ConsumedWh is >0, I wish to change the UnitMode = 0 to the number 3, otherwise leave the UnitMode as is. For the UnitMode in cell N2, an expression for this would be:

=IF(AND(N1=2,N2=0,L2>0),place 3 in N2,N2) which, in this case, would result in the UnitMode for cell N2 remaining as 2.

If a second UnitMode = 0, follows a UnitMode =2, AND the ConsumedWh>0 (as in Row 26) I wish to change the UnitMode = 0 to the number 4. Finally, if a third consecutive UnitMode = 0 follows a UnitMode =2, AND the ConsumedWh>0, I wish to change the UnitMode = 0 to the number 5 (as in Row 27). See column W for what I would like to see in Column N (without formatting).

I would also like to add some summary results, shown in the range Y1:AB4.
The equation in Y1 is: =SUMIF(W:W,"=0",L:L)+AA2+AA3+AA4
In AA2, the equation is SUMIF(W:W,"=3",L:L)
In AA3, the equation is=SUMIF(W:W,"=4",L:L)
In AA4, the equation is=SUMIF(W:W,"=5",L:L)


My attempt at coding for the first Mode = 0 following a Mode = 2, is shown below, where I had tried to create a new Column called FirstZeroWh to store the results in.

Rich (BB code): 
Sub ZeroModeCaptureWh() 
' get the last row
Dim startRow As Long, lastRow As Long
startRow = 2
lastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row


Dim i As Long, UnitMode As Long, UnitModeNext As Long, ConsumedWh As Long
Dim FirstZeroWh As String


' Go through the UnitMode column
For i = startRow To lastRow
UnitMode = Sheet1.Range("N" & i).Value
ConsumedWh = Sheet1.Range("L" & i).Value
UnitModeNext = Sheet1.Range("N" & (i + 1)).Value


' Check UnitMode and classify accordingly
If UnitMode = 2 And UnitModeNext = 0 And ConsumedWh > 0 Then
FirstZeroWh = "7"
Else
' For all other cases
FirstZeroWh = UnitMode
End If


' Write out the class to column W
Sheet1.Range("W" & i).Value = FirstZeroWh
Next


End Sub



I was doing okay until I added:
[ConsumedWh = Sheet1.Range("L" & i).Value
UnitModeNext = Sheet1.Range("N" & (i + 1)).Value]

which was a bit of a guess!

Any help would be very much appreciated.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
This only addresses the data extraction portion of your question and not the summary formulas at the bottom.

Code: 
Sub ZeroModeCaptureWh()
    Dim ZCnt As Long
    ZCnt = 0
    
    Dim WS As Worksheet
    Set WS = Worksheets("Sheet1")

    ' get the last row
    Dim startRow As Long, lastRow As Long
    startRow = 2
    lastRow = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row

    Dim i As Long, UnitMode As Long, UnitModePrev As Long, ConsumedWhPrev As Long
    Dim FirstZeroWh As String

    WS.Range("W" & startRow).Value = WS.Range("N" & startRow).Value    'start row special case

    ' Go through the UnitMode column
    For i = startRow + 1 To lastRow
        UnitMode = WS.Range("N" & i).Value
        UnitModePrev = WS.Range("N" & (i - 1)).Value
        ConsumedWhPrev = WS.Range("L" & (i - 1)).Value

        If UnitMode = 0 And ZCnt > 0 Then
            ZCnt = ZCnt + 1
        Else
            ZCnt = 0
        End If

        ' Check UnitModePrev and classify accordingly
        If UnitModePrev = 2 And UnitMode = 0 And ConsumedWhPrev > 0 Then
            ZCnt = ZCnt + 1
            FirstZeroWh = "3"
        Else
            ' For all other cases
            Select Case ZCnt
            Case 2
                FirstZeroWh = "4"
            Case 3
                FirstZeroWh = "5"
            Case Else
                FirstZeroWh = UnitMode
            End Select
        End If

        ' Write out the class to column W
        Debug.Print i
        WS.Range("W" & i).Value = FirstZeroWh
    Next i
End Sub
 
Upvote 0
rlv01, that's great. Thanks for spending the time on this. I made one minor change, replacing the variable ConsumedWhPrev with ConsumedWh and the following code (so that the same row as UnitMode is checked, as opposed to the previous row):

Code: 
ConsumedWh = WS.Range("L" & i).Value

It works perfectly.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
286
excelnewbie999
E
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
248
dshtesy
D
I
  • Question Question
Sum column by priority (Y-axis) and date (Y-axis)
Replies
3
Views
183
kvsrinivasamurthy
K
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
310
JMWh1t3
J
O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
261
Skyybot
Skyybot

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

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