Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    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?
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    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.

  3. #3
    5 Star Lounger
    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The attached workbook contains a pivot table with additional formulas.

    (It also contains the macro from my previous reply)
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    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?

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MelanieB' post='773080' date='30-Apr-2009 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.

  8. #8
    5 Star Lounger
    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!

  9. #9
    5 Star Lounger
    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.

  10. #10
    Bronze Lounger
    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.

  11. #11
    5 Star Lounger
    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?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='MelanieB' post='773080' date='30-Apr-2009 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 left-most column. I have no idea why it works differently for you.

  13. #13
    5 Star Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •