Macro to summarize daily data in Excel 2003

Topic

New Reply

One of my coworkers has a worksheet that captures data every hour of every day. He needs to summarize the data for each day.

I’m attaching a sample of the spreadsheet since I’m not very good at explaining this. The yellow highlighted part is what he wants the finished product to look like (on a separate worksheet).

He said he wants a macro he can run to create the summary sheet.

I don’t know enough VBA to write a macro, so I have to rely on turning on the recorder. And, then I don’t know how to get it to loop through and continue until the end of the data range. He collects the data in 100 day blocks.

Can someone help me with this please?

Reply | Quote

Replies

I’m also playing with getting it into a pivot table, but I’m having a problem getting the calculation for the delta to work. I know how to add a calculated field to the table, but I don’t know how to construct the formula correctly. I’m not sure he will want a pivot table, but if I get it to work, maybe he’d like it. I don’t know. Just throwing ideas out.

Thanks.

Reply | Quote

The attached workbook contains a pivot table with additional formulas.

(It also contains the macro from my previous reply)

Reply | Quote

Wow. Thank you both so much. I just took my first intro to VBA class last week. It was about 6 hours long, and everything he said made sense when I was there, so I was hoping I could figure this out on my own. But, I had NO idea where to even start. It’s like my brain freezes up whenever I think about it. And, now that I have seen the code you both provided.. well I just never would have figured this out.

Am I doomed to be forever code illiterate? Do you have any suggestions?

Again, thank you both so much. I hope that by studying this code I will be able to learn something. I think I need to spend more time in the VBA thread.

Oh.. Hans. Your code flips the worksheet when it is done so that the summary sheet has column A to the far right. I know how to go to options and change it. But, how do I add that to the code so it doesn’t flip it?

Reply | Quote

Am I doomed to be forever code illiterate? Do you have any suggestions?

I found John Walkenbach’s Excel VBA Programming for dummies extremely useful when I first started out. There are also endless useful online resources available, including here in the lounge.

Reply | Quote

Thank you. I had forgotten about that site. And,yes, you’re right. This board is the best place to learn!

Reply | Quote

I just looked at that book. Do you know if VBA is the same for Excel 2007? We’re still on 03, but we plan to move to 07 by the end of this year, so I’d like to make sure I’m not struggling to learn something obsolete.

Thanks.

Reply | Quote

Oh.. Hans. Your code flips the worksheet when it is done so that the summary sheet has column A to the far right. I know how to go to options and change it. But, how do I add that to the code so it doesn’t flip it?

The code doesn’t do that, the dates are in the left-most column. I have no idea why it works differently for you.

Reply | Quote

ok thanks! Must be something else.. I’ll try another pc.

Reply | Quote

The following will move the data to a summary sheet.
[codebox]
Sub moveDeltas()
Dim lRow As Long, i As Long, dDailyMin As Double, dDailyMax As Double, dImpMin As Double, dImpMax As Double
Dim outRow As Long
Dim shData As Worksheet, shSumm As Worksheet
Application.ScreenUpdating = False

Set shData = ActiveSheet
Set shSumm = Worksheets(“Summary”)
lRow = shData.Cells(Rows.Count, 1).End(xlUp).Row
outRow = 2
For i = 2 To lRow
If shData.Cells(i, 1) shData.Cells(i – 1, 1) Then
dDailyMin = shData.Cells(i, 4)
dDailyMax = shData.Cells(i, 4)
dImpMin = shData.Cells(i, 5)
dImpMax = shData.Cells(i, 5)
shSumm.Cells(outRow, 1) = shData.Cells(i, 1)
outRow = outRow + 1
End If

If shData.Cells(i, 4) dDailyMax Then
dDailyMax = shData.Cells(i, 4)
End If

If shData.Cells(i, 5) dImpMax Then
dImpMax = shData.Cells(i, 5)
End If

If shData.Cells(i, 1) shData.Cells(i + 1, 1) Then
shSumm.Cells(outRow – 1, 2) = Round(dDailyMax – dDailyMin, 2)
shSumm.Cells(outRow – 1, 3) = Round(dImpMax – dImpMin, 2)
End If
Next
Range(shSumm.Cells(2, 1), shSumm.Cells(outRow, 1)).NumberFormat = “m/dd/yyyy”
Application.ScreenUpdating = True

End Sub[/codebox]

Please see attached

Reply | Quote

Here is a possible macro:

Code:

Sub Summarize()
  Dim wsh1 As Worksheet
  Dim wsh2 As Worksheet
  Dim m As Long
  Dim n As Long
  Dim r As Long

  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False

  Set wsh1 = Worksheets("Sheet1")
  m = wsh1.Range("A1").End(xlDown).Row
  Set wsh2 = Worksheets.Add(After:=wsh1)
  wsh2.Range("A1") = "Date"
  wsh2.Range("B1") = "output 393 current daily delta"
  wsh2.Range("C1") = "output 393 unimpounded daily delta"
  wsh1.Range("A1:A" & m).AdvancedFilter _
	Action:=xlFilterCopy, CriteriaRange:=wsh1.Range("A1"), _
	CopyToRange:=wsh2.Range("A1"), Unique:=True
  n = wsh2.Range("A1").End(xlDown).Row
  For r = 2 To n
	wsh2.Range("B" & r).FormulaArray = _
	  "=MAX(IF(Sheet1!A2:A" & m & "=A" & r & ",Sheet1!C2:C" & m & _
	  "))-MIN(IF(Sheet1!A2:A" & m & "=A" & r & ",Sheet1!C2:C" & m & "))"
	wsh2.Range("C" & r).FormulaArray = _
	  "=MAX(IF(Sheet1!A2:A" & m & "=A" & r & ",Sheet1!D2:D" & m & _
	  "))-MIN(IF(Sheet1!A2:A" & m & "=A" & r & ",Sheet1!D2:D" & m & "))"
  Next r

  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

Reply | Quote

Hans. Your code flips the worksheet when it is done so that the summary sheet has column A to the far right. I know how to go to options and change it. But, how do I add that to the code so it doesn’t flip it?

Reply | Quote

I use 2003 at work and 2002 at home, which are pretty much the same. I have no experience with 2007. Based only on post’s that I have read here in the lounge, 2007 VBA is much the same as 02/03, with some new bits added, but I don’t think there is much of a difference.

Reply | Quote