Excel VBA to find a set of Low and High values then subtract and continually repeat throughout data set

[Jamespr]

Date and Time Value Gallons Used in each Batch
06/01/2020 00:00 205
06/01/2020 01:00 100
06/01/2020 02:00 50 155
06/01/2020 02:04 400
06/01/2020 03:00 300
06/01/2020 04:00 200
06/01/2020 05:00 100
06/01/2020 06:00 47 353
06/01/2020 06:04 395
06/01/2020 07:00 300
06/01/2020 08:00 200
06/01/2020 09:00 100
06/01/2020 10:00 49 356
06/01/2020 10:03 400
……………..
06/01/2020 20:23 398
06/01/2020 21:00 300
06/01/2020 22:00 46 352
06/01/2020 22:04 402
06/01/2020 23:00 202
06/01/2020 23:56 43 349
06/01/2020 23:57 105
06/01/2020 23:58 210
06/01/2020 23:59 305
06/02/2020 00:00 350
06/02/2020 00:01 380
06/02/2020 00:02 402
06/02/2020 00:43 350
06/02/2020 01:00 280
06/02/2020 02:00 200

Hello everyone,

I am in need of help with a data set that has two columns with existing values. Column A (Date and Time) and Column B (Value). There can be several thousand rows in each data set including multiple days within the data set. The rows are sorted in ascending order by Column A (Date and Time) with the first row being a header and the second row usually being 0000 hrs of the first date in the data set. Column B (value) will ascend and descend multiple times throughout the day with the lowest value (end point) being approximately 50 and the highest value (starting point) being approximately 400. These values are not absolute and can very plus or minus around 10 to 15. Column B (value) is a Tank Volume in gallons which automatically fills when it reaches a low value (end point) of approximately 50 gallons, the fill time is completed within just a few minutes taking the value from a low value (end point) of about 50 up a high value (starting point) of around 400 and the drain time is over several hours.

First, the starting point value in Column B (Value) needs to be identified, for this example 205, at the beginning of the day Column A (Date and Time) (06/01/2020 00:00). Then it needs to be determined if the values are ascending or descending in time. If they are ascending then once it reaches the High value (approximately 400) that becomes the starting point value. Once the starting point value is established the proceeding rows will need to be evaluated to identify the row with the lowest value (end point) near 50, before the proceeding rows values start to ascend as the tank starts to fill. Now that the high value (starting point) is known, in this example 205 (06/01/2020 00:00) and the low value (end point) before the tanks start to fill is known, in this example 50 (06/01/2020 02:00), the low value (end point) of 50 can be subtracted from the previous high value (starting point) of 205 providing a value of 195 gallons used in that batch.

After the low point is identified and the values start to increase as the tank fills a new high value (starting point) needs to be identified, in the above example it would be 400 (06/01/2020 02:04). Once the new high value (starting point) is identified then the low value (end point) of the proceeding rows will need to be identified, 47 (06/01/2020 06:00). The low value (end point) of 47 will then need to be subtracted from the previous high value (starting point) of 400 providing a value of 353 gallons used in that batch. The identifying of each proceeding high value (starting point) and low value (end point) along with subtracting the low value (end point) from the high value (starting point) to come up with gallons used in that batch throughout the day.

The last high value (starting point) of each day will need to be subtracted from the last low value (end point) of the day. The last low value (end point) of the day may be a true low value (end point) as in the above example of 43 (06/01/2020 23:56). Or, the last low value (end point) of the day may just be the value at 23:59 hrs (for instance 187) if the previous row values have been descending and the low value of 50 was not reached by 23:59 hrs.

Once all of the high value (starting point) and low value (end point) have been identified in the data set and they have been calculated to identify gallons used in that batch the total gallons used in all batches for each day needs to be calculated.

I have attempted to provide as much detail as possible while trying not to make it confusing. Since I am the one who is dealing with this first hand it makes complete sense to me however I realize my explanation will fall short of being completely clear for others. I will be happy to provide additional details to make things clear for others. Please ask whatever questions you have so that I can make my needs more clear for everyone.

This will be used on multiple computers with operating systems ranging from Windows 7 to Windows 10 with Excel ranging from 2010 through 2019.

Initially, for a weeks’ worth of data, I filtered column B (value) to show values above 320 and values below 60. Then I manually scrolled thought each row to identify the high value (starting point) and low value (end point) and in a third column, column C (Gallons Used in each Batch) I subtracted the cell with the low value (end point) from the cell with the high value (starting point) to provide a value for (Gallons Used in each Batch). Once that was completed for all of the high value (starting point) and low value (end point) in the data set I used the SUMif function with criteria of a specific date range for each date to total all column C (Gallons Used in each Batch) for each day. SUMIFS(C2:C72797,A2:A72797,">=" &"6/1/2020",A2:A72797,"<" &"6/2/2020"). I realize that this is a very inefficient and time-consuming approach that is not sustainable. I am posting for help to come up with a more efficient way. I imagine the most efficient way would be with VBA code. I am limited on experience with VBA but have successfully used it in the past. All suggestions and help are welcome.



Thank you for your time and assistance your help is greatly appreciated.

 

[offthelip]

try this code, you didn't say where you wanted the total so I used msgbox

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
Range(Cells(2, 3), Cells(lastrow, 3)) = ""
outarr = Range(Cells(1, 3), Cells(lastrow, 3))
curdate = Round(inarr(2, 1), 0)
startv = inarr(2, 2)
totalv = 0
For i = 3 To lastrow - 1
  If Round(inarr(i + 1, 1), 0) > curdate Then
     ' start new day
     curdate = Round(inarr(i + 1, 1), 0)
     ' if doww then write out results
     If inarr(i, 2) < inarr(i - 1, 2) Then
      outarr(i, 1) = startv - inarr(i, 2)
      totalv = totalv + outarr(i, 1)
     End If
     startv = inarr(i + 1, 2)
 
  Else
     ' if change up write out results
     If inarr(i + 1, 2) > inarr(i, 2) And inarr(i, 2) < inarr(i - 1, 2) Then
      outarr(i, 1) = startv - inarr(i, 2)
      totalv = totalv + outarr(i, 1)
     End If
     ' if change down capture start point
     If inarr(i + 1, 2) < inarr(i, 2) And inarr(i, 2) > inarr(i - 1, 2) Then
       startv = inarr(i, 2)
     End If
    


  End If
Next i
' put in last value
     If inarr(lastrow, 2) < inarr(lastrow - 1, 2) Then
      outarr(lastrow, 1) = startv - inarr(lastrow, 2)
      totalv = totalv + outarr(i, 1)
     End If

Range(Cells(1, 3), Cells(lastrow, 3)) = outarr
MsgBox (totalv)
End Sub

 

[Jamespr]

Thank you Offthelip,

When the code is ran the following error occurs "Run-time error '13': Type mismatch" and when debug is ran the last line in the following code is highlighted

  Else
     ' if change up write out results
     If inarr(i + 1, 2) > inarr(i, 2) And inarr(i, 2) < inarr(i - 1, 2) Then
      outarr(i, 1) = startv - inarr(i, 2)

As for the results, once each set of high and low values are identified and the low value is subtracted from the high value the value of that calculation can be placed in column C next to the corresponding low value. It would also be helpful for troubleshooting and review purposes to have the corresponding high value cell reference in column D and the corresponding low value cell refernce in column E. Once the data set has been completely ran through it would be good to list each date in the data set and the sum of their corresponding totals from column C (Gallons Used in each Batch) on a separate sheet.

Date Daily total
06/01/2020 4322
06/02/2020 3993
06/03/2020 5220


Thank you for your time and efforts they are much appreciated

 

[offthelip]

Startv and inarr(i,2) are just using values from column B in a simple subtraction equation. A type mismatch implies that one of those doesn't have number in it. So when you get the error use debug to check what values are in startv and inarr(i,1).
startv is initialised with value in B2 so does this have a number in it?, is there any other cell in column B that doesn't have number it.