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.
<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>
</tbody>
<tbody>
</tbody>
(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.
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.
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | DateTime | Outdoor | Set room | Room | Set room (Zone2) | Room (Zone2) | Set flow | Flow | Return | Set tank | Tank | ConsumedWh | DeliveredWh | UnitMode | Defr | Data 30 | Set - Z1 | Set - Z2 | Set - Flow | Flow - return | UnitMode | Total Mode 0 Consumed Wh | First Mode 0 after Mode 2 (Wh con) | |||||
2 | 01/10/2017 07:27 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 27 | 26.5 | 50 | 47 | 1 | 19 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0.5 | 2 | 90 | 1st zero: | 80 | 89% | |||
3 | 01/10/2017 07:28 | 14 | 20 | 20.5 | 18 | 19.5 | 27 | 26.5 | 26.5 | 50 | 47 | 1 | 3 | 2 | 0 | 2 | -0.5 | -1.5 | 0.5 | 0 | 2 | NEED LAST | 2nd zero: | 5 | 6% | |||
4 | 01/10/2017 07:29 | 14 | 20 | 20.5 | 18 | 19.5 | 27 | 27 | 26.5 | 50 | 46.5 | 2 | 2 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0.5 | 2 | ROW FOR | 3rd zero: | 1 | 1% | |||
5 | 01/10/2017 07:30 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 28.5 | 27 | 50 | 47 | 12 | 14 | 0 | 0 | 0 | -0.5 | -1.5 | -1.5 | 1.5 | 3 | ABOVE! | Above uses | |||||
6 | 01/10/2017 07:31 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 27 | 27 | 50 | 46.5 | 10 | 34 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0 | 2 | Sum (Col:Col) only | ||||||
7 | 01/10/2017 07:32 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 27 | 27 | 50 | 46.5 | 1 | 18 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0 | 2 | 0 | ||||||
8 | 01/10/2017 07:33 | 14 | 20 | 20.5 | 18 | 19.5 | 27 | 26.5 | 26.5 | 50 | 46.5 | 2 | 3 | 2 | 0 | 2 | -0.5 | -1.5 | 0.5 | 0 | 2 | |||||||
9 | 01/10/2017 07:34 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 27 | 26.5 | 50 | 46.5 | 1 | 2 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0.5 | 2 | |||||||
10 | 01/10/2017 07:35 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 28.5 | 27 | 50 | 46.5 | 13 | 14 | 0 | 0 | 0 | -0.5 | -1.5 | -1.5 | 1.5 | 3 | |||||||
11 | 01/10/2017 07:36 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 27 | 27 | 50 | 46.5 | 9 | 37 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0 | 2 | |||||||
12 | 01/10/2017 07:37 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 27 | 27 | 50 | 46.5 | 1 | 11 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0 | 2 | |||||||
13 | 01/10/2017 07:38 | 14 | 20 | 20.5 | 18 | 19.5 | 27 | 26.5 | 26.5 | 50 | 46.5 | 1 | 4 | 2 | 0 | 2 | -0.5 | -1.5 | 0.5 | 0 | 2 | |||||||
14 | 01/10/2017 07:39 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 27 | 26.5 | 50 | 46.5 | 3 | 2 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0.5 | 2 | |||||||
15 | 01/10/2017 07:40 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 29 | 27 | 50 | 46.5 | 12 | 14 | 0 | 0 | 0 | -0.5 | -1.5 | -2 | 2 | 3 | |||||||
16 | 01/10/2017 07:41 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 27 | 27 | 50 | 46.5 | 8 | 38 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0 | 2 | |||||||
17 | 01/10/2017 07:42 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 27 | 27 | 50 | 46.5 | 1 | 4 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0 | 2 | |||||||
18 | 01/10/2017 07:43 | 14 | 20 | 20.5 | 18 | 19.5 | 27 | 26.5 | 26.5 | 50 | 46.5 | 2 | 3 | 2 | 0 | 2 | -0.5 | -1.5 | 0.5 | 0 | 2 | |||||||
19 | 01/10/2017 07:44 | 14 | 20 | 20.5 | 18 | 19.5 | 27 | 27.5 | 26.5 | 50 | 47 | 3 | 0 | 2 | 0 | 2 | -0.5 | -1.5 | -0.5 | 1 | 2 | |||||||
20 | 01/10/2017 07:45 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 29 | 27 | 50 | 46.5 | 12 | 15 | 0 | 0 | 0 | -0.5 | -1.5 | -2 | 2 | 3 | |||||||
21 | 01/10/2017 07:46 | 13 | 20 | 20.5 | 18 | 19.5 | 27 | 27 | 27 | 50 | 46.5 | 7 | 39 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0 | 2 | |||||||
22 | 01/10/2017 07:47 | 14 | 20 | 20.5 | 18 | 19.5 | 27 | 27 | 27 | 50 | 46.5 | 2 | 0 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0 | 2 | |||||||
23 | 01/10/2017 07:48 | 14 | 20 | 20.5 | 18 | 19.5 | 27 | 26.5 | 26.5 | 50 | 46.5 | 1 | 3 | 2 | 0 | 2 | -0.5 | -1.5 | 0.5 | 0 | 2 | |||||||
24 | 01/10/2017 07:49 | 14 | 20 | 20.5 | 18 | 19.5 | 27 | 27.5 | 26.5 | 50 | 46.5 | 4 | 0 | 2 | 0 | 2 | -0.5 | -1.5 | -0.5 | 1 | 2 | |||||||
25 | 01/10/2017 07:50 | 14 | 20 | 20.5 | 18 | 19.5 | 26 | 28.5 | 27 | 50 | 46.5 | 13 | 21 | 0 | 0 | 0 | -0.5 | -1.5 | -2.5 | 1.5 | 3 | |||||||
26 | 01/10/2017 07:51 | 14 | 20 | 20.5 | 18 | 19.5 | 26 | 26.5 | 27 | 50 | 46.5 | 5 | 38 | 0 | 0 | 0 | -0.5 | -1.5 | -0.5 | -0.5 | 4 | |||||||
27 | 01/10/2017 07:52 | 14 | 20 | 20.5 | 18 | 19.5 | 26 | 26.5 | 26.5 | 50 | 46.5 | 1 | 0 | 0 | 0 | 0 | -0.5 | -1.5 | -0.5 | 0 | 5 | |||||||
28 | 01/10/2017 07:53 | 15 | 20 | 20.5 | 18 | 19.5 | 26 | 26.5 | 26.5 | 50 | 46.5 | 1 | 3 | 0 | 0 | 0 | -0.5 | -1.5 | -0.5 | 0 | 0 | |||||||
29 | 01/10/2017 07:54 | 15 | 20 | 20.5 | 18 | 19.5 | 26 | 26.5 | 26.5 | 50 | 46.5 | 2 | 0 | 0 | 0 | 0 | -0.5 | -1.5 | -0.5 | 0 | 0 | |||||||
30 | 01/10/2017 07:55 | 15 | 20 | 20.5 | 18 | 19.5 | 26 | 26 | 26 | 50 | 46.5 | 1 | 0 | 0 | 0 | 0 | -0.5 | -1.5 | 0 | 0 | 0 | |||||||
31 | 01/10/2017 07:56 | 15 | 20 | 20.5 | 18 | 19.5 | 26 | 26 | 26 | 50 | 46.5 | 1 | 0 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0 | 2 | |||||||
32 | 01/10/2017 07:57 | 14 | 20 | 20.5 | 18 | 19.5 | 26 | 27 | 26 | 50 | 46.5 | 3 | 0 | 2 | 0 | 2 | -0.5 | -1.5 | -1 | 1 | 2 | |||||||
33 | 01/10/2017 07:58 | 14 | 20 | 20.5 | 18 | 19.5 | 26 | 27.5 | 26.5 | 50 | 46.5 | 12 | 15 | 0 | 0 | 0 | -0.5 | -1.5 | -1.5 | 1 | 3 | |||||||
34 | 01/10/2017 07:59 | 14 | 20 | 20.5 | 18 | 19.5 | 26 | 26 | 26.5 | 50 | 46.5 | 5 | 30 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | -0.5 | 2 | |||||||
35 | 01/10/2017 08:00 | 14 | 20 | 20.5 | 18 | 19.5 | 26 | 26 | 26 | 50 | 46.5 | 1 | 0 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0 | 2 | |||||||
36 | 01/10/2017 08:00 | 14 | 20 | 20.5 | 18 | 19.5 | 26 | 26 | 26 | 50 | 46.5 | 1 | 5 | 2 | 0 | 2 | -0.5 | -1.5 | 0 | 0 | 2 | |||||||
37 | 01/10/2017 08:01 | 14 | 20 | 20.5 | 18 | 19.5 | 26 | 27.5 | 26 | 50 | 46.5 | 6 | 0 | 0 | 0 | 0 | -0.5 | -1.5 | -1.5 | 1.5 | 3 | |||||||
38 | 01/10/2017 08:02 | 14 | 20 | 20.5 | 18 | 19.5 | 26 | 26.5 | 26.5 | 50 | 46.5 | 11 | 33 | 2 | 0 | 2 | -0.5 | -1.5 | -0.5 | 0 | 2 |
<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>
</tbody>
Sheet1 (2)
Worksheet Formulas
<thead> </thead><tbody> </tbody> |
<tbody>
</tbody>
(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.