# Thread: Sub Totals (Ms Excel 2007)

1. ## 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

2. ## 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. ## Re: Sub Totals (Ms Excel 2007)

we can ignore.

4. ## 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"), _
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. ## 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. ## 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"), _
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. ## 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"), _
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. ## 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. ## Re: Sub Totals (Ms Excel 2007)

Sorry, you've lost me.

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

11. ## 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. ## 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.

13. ## 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.

14. ## 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. ## 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.

#### Posting Permissions

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