Results 1 to 13 of 13

20090429, 09:32 #1
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20090429, 10:34 #2
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20090429, 14:14 #3
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
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) < dDailyMin Then
dDailyMin = shData.Cells(i, 4)
End If
If shData.Cells(i, 4) > dDailyMax Then
dDailyMax = shData.Cells(i, 4)
End If
If shData.Cells(i, 5) < dImpMin Then
dImpMin = shData.Cells(i, 5)
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

20090429, 14:42 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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

20090429, 14:45 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
The attached workbook contains a pivot table with additional formulas.
(It also contains the macro from my previous reply)

20090430, 10:05 #6
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20090430, 10:20 #7
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='MelanieB' post='773080' date='30Apr2009 16:05']Am I doomed to be forever code illiterate? Do you have any suggestions?[/quote]
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.

20090430, 10:35 #8
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thank you. I had forgotten about that site. And,yes, you're right. This board is the best place to learn!

20090430, 10:38 #9
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20090430, 10:46 #10
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20090430, 13:37 #11
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20090430, 14:46 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
[quote name='MelanieB' post='773080' date='30Apr2009 17:05']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?[/quote]
The code doesn't do that, the dates are in the leftmost column. I have no idea why it works differently for you.

20090430, 14:51 #13
 Join Date
 Jan 2001
 Location
 Ephrata, Washington, USA
 Posts
 683
 Thanks
 0
 Thanked 0 Times in 0 Posts
ok thanks! Must be something else.. I'll try another pc.