Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sub Totals (Ms Excel 2007)

    Hi!
    I'm looking for a formula/macro to accomplish the following. For every change in "Main" , delete the row if the whse in the whs field is equal to the "Master" (in the Level column) whs (column . if whs isn ot equal to the "master" then provide a summary for each whs. Ie: IND = 5 LA = 3 OC = 4
    Thank You So Much
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Totals (Ms Excel 2007)

    There are some rows that have a blank in the wsh column. Should those be included in the summary, or should they be ignored?

  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Totals (Ms Excel 2007)

    we can ignore.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Totals (Ms Excel 2007)

    Try this macro:

    Sub CreateSummary()
    Dim lngRow As Long
    Dim strWhs As String
    Dim strMain As String
    Application.ScreenUpdating = False
    ' Sort the range
    Range("A1").CurrentRegion.Sort _
    Key1:=Range("B1"), Key2:=Range("C1"), _
    Key3:=Range("A1"), Header:=xlYes
    lngRow = 2
    ' Loop
    Do While Not Range("B" & lngRow) = ""
    If InStr(Range("C" & lngRow), "MASTER") > 0 Then
    ' Store master whs and main
    strWhs = Range("A" & lngRow)
    strMain = Range("B" & lngRow)
    End If
    If Range("A" & lngRow) = strWhs And _
    Range("B" & lngRow) = strMain Then
    ' Delete row with master whs
    Range("A" & lngRow).EntireRow.Delete
    ElseIf Trim(Range("A" & lngRow)) = "" Then
    ' Delete row with blank whs
    Range("A" & lngRow).EntireRow.Delete
    ElseIf Range("B" & lngRow) = Range("B" & (lngRow - 1)) And _
    Range("A" & lngRow) = Range("A" & (lngRow - 1)) Then
    ' Delete duplicate row
    Range("A" & lngRow).EntireRow.Delete
    ' And increase count
    Range("D" & (lngRow - 1)) = Range("D" & (lngRow - 1)) + 1
    Else
    ' Increase row
    lngRow = lngRow + 1
    ' And increase count
    Range("D" & (lngRow - 1)) = Range("D" & (lngRow - 1)) + 1
    End If
    Loop
    Application.ScreenUpdating = True
    End Sub

    (You could also create a pivot table to see the counts)

  5. #5
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Totals (Ms Excel 2007)

    Hi Hans! This does almost exactly what I want to do, but I need for the "Master" account to remain, so I can tie the Sub Accounts to them. Is that doable?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Totals (Ms Excel 2007)

    That's not what you originally asked, but it requires only a small change to the macro:

    Sub CreateSummary()
    Dim lngRow As Long
    Dim strWhs As String
    Dim strMain As String
    Application.ScreenUpdating = False
    ' Sort the range
    Range("A1").CurrentRegion.Sort _
    Key1:=Range("B1"), Key2:=Range("C1"), _
    Key3:=Range("A1"), Header:=xlYes
    lngRow = 2
    ' Loop
    Do While Not Range("B" & lngRow) = ""
    If InStr(Range("C" & lngRow), "MASTER") > 0 Then
    ' Store master whs and main
    strWhs = Range("A" & lngRow)
    strMain = Range("B" & lngRow)
    ' Increase row
    lngRow = lngRow + 1
    ElseIf Range("A" & lngRow) = strWhs And _
    Range("B" & lngRow) = strMain Then
    ' Delete row with master whs
    Range("A" & lngRow).EntireRow.Delete
    ElseIf Trim(Range("A" & lngRow)) = "" Then
    ' Delete row with blank whs
    Range("A" & lngRow).EntireRow.Delete
    ElseIf Range("B" & lngRow) = Range("B" & (lngRow - 1)) And _
    Range("A" & lngRow) = Range("A" & (lngRow - 1)) Then
    ' Delete duplicate row
    Range("A" & lngRow).EntireRow.Delete
    ' And increase count
    Range("D" & (lngRow - 1)) = Range("D" & (lngRow - 1)) + 1
    Else
    ' Increase row
    lngRow = lngRow + 1
    ' And increase count
    Range("D" & (lngRow - 1)) = Range("D" & (lngRow - 1)) + 1
    End If
    Loop
    Application.ScreenUpdating = True
    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Totals (Ms Excel 2007)

    Or if you want to count the rows with the "master" wsh too:

    Sub CreateSummary()
    Dim lngRow As Long
    Dim strWhs As String
    Dim strMain As String
    Dim lngMasterRow As Long
    Application.ScreenUpdating = False
    ' Sort the range
    Range("A1").CurrentRegion.Sort _
    Key1:=Range("B1"), Key2:=Range("C1"), _
    Key3:=Range("A1"), Header:=xlYes
    lngRow = 2
    ' Loop
    Do While Not Range("B" & lngRow) = ""
    If InStr(Range("C" & lngRow), "MASTER") > 0 Then
    ' Store master whs and main
    strWhs = Range("A" & lngRow)
    strMain = Range("B" & lngRow)
    lngMasterRow = lngRow
    ' Increase row
    lngRow = lngRow + 1
    ' And increase count
    Range("D" & lngMasterRow) = Range("D" & lngMasterRow) + 1
    ElseIf Range("A" & lngRow) = strWhs And _
    Range("B" & lngRow) = strMain Then
    ' Delete row with master whs
    Range("A" & lngRow).EntireRow.Delete
    ' And increase count
    Range("D" & lngMasterRow) = Range("D" & lngMasterRow) + 1
    ElseIf Trim(Range("A" & lngRow)) = "" Then
    ' Delete row with blank whs
    Range("A" & lngRow).EntireRow.Delete
    ElseIf Range("B" & lngRow) = Range("B" & (lngRow - 1)) And _
    Range("A" & lngRow) = Range("A" & (lngRow - 1)) Then
    ' Delete duplicate row
    Range("A" & lngRow).EntireRow.Delete
    ' And increase count
    Range("D" & (lngRow - 1)) = Range("D" & (lngRow - 1)) + 1
    Else
    ' Increase row
    lngRow = lngRow + 1
    ' And increase count
    Range("D" & (lngRow - 1)) = Range("D" & (lngRow - 1)) + 1
    End If
    Loop
    Application.ScreenUpdating = True
    End Sub

  8. #8
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Totals (Ms Excel 2007)

    I apologize Hans.. I wanted the Master to remain only for the "sub accounts" that we're counting. All other Masters can be deleted. *ducking before you throw something at me*..

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Totals (Ms Excel 2007)

    Sorry, you've lost me.

  10. #10
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Totals (Ms Excel 2007)

    Where we're keeping the "Sub Accounts" and providing a count, keep the Master Account that's tied to the Sub Accounts where the "Main" is equal to the Sub Accounts main.
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Totals (Ms Excel 2007)

    I thought that the code I posted in <post:=729,700>post 729,700</post:> (or in <post:=729,710>post 729,710</post:>) does that? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  12. #12
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Totals (Ms Excel 2007)

    Hopefully the attachment will explain better. I've highlight in red what should delete because there isn't a Sub Account tied to the Master that we are counting. the yellow light green indicates we keep the Master because of the Sub Accounts that we are counting.
    Attached Files Attached Files

  13. #13
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Totals (Ms Excel 2007)

    sorry posted in wrong spot.. Where we're keeping the "Sub Accounts" and providing a count, keep the Master Account that's tied to the Sub Accounts where the "Main" is equal to the Sub Accounts main.
    Attached Files Attached Files

  14. #14
    Star Lounger
    Join Date
    Jul 2005
    Location
    Earth, New Jersey, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Totals (Ms Excel 2007)

    post 729,700 removes all of the "Masters" . post 729,710 all of the "Masters" remain regardless if there's a Sub-Account that tied to the Master that's being "counted" or not.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Totals (Ms Excel 2007)

    Your original example didn't have any "MASTER" row without subaccounts, so it was very difficult for me to understand what you meant. In the future, please try to provide relevant and complete information from the start instead of releasing it piecemeal.

    Try the code in the attached text file.
    Attached Files Attached Files

Posting Permissions

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