• Status: Solved
• Priority: Medium
• Security: Public
• Views: 427

# excel group and subtotal

I have an excel worksheet that I want grouped (I tried the first few rows manually and it would take me years so is there a better way?) I want the text between each TestSuitName grouped (see rows 3-6) I also want to have a subtotal of the sum of each Result Passed, Result Failed but outside the group so that when the group is collapsed all that is shown is the TestSuitname and the subtotal and not the details. Is this possible?? If so how?
summary.xls
0
joeloyzaga
• 2
1 Solution

IT & Database AssistantCommented:
Assuming the blanks in column for TestSuiteName should be the same as the completed cell above the blanks.

Apply a filter to this column and select Blanks. In the first blank enter a formula referring to the cell above, in your example the first blank is E3 so the formula would be =E2. Using the drag handle drag this formula down the column. This will fill only the blanks, thus filling in all the blanks with the correct value from the top row of each group. Deselect the filter to show the result. Copy and paste value if so required.

Now that you have the complete column of data you can use the Data > Subtotals routine which will add grouping and you can specify which columns to receive the totalling. When you collapse the groups using the buttons in the margin, the totals will stay visible.

Thanks
Rob H
0

IT & Database AssistantCommented:
As attached.

Rob H
summary.xls
0

Commented:
Presumably you are generating this file from time to time and want to be able to format it for ease of use.

I'd set up a macro, then you can easily run the macro against a new set of data.

This seems to to the trick:

Sub AutoGroupAndTotal()

Dim WS As Worksheet

Set WS = ActiveCell.Worksheet

i = 1 ' i points to the beginning of this group
Do While WS.Cells(i, 5) <> "" Or WS.Cells(i, 4) <> ""
Do While WS.Cells(i, 4) <> ""
i = i + 1
Loop
j = i + 1
Do While Cells(j, 5) = "" And Cells(j, 11) <> ""
j = j + 1
Loop
' j points to the end of this group
WS.Cells(i, 9) = "=sum(i" & LTrim(Str(i + 1)) & ":i" & LTrim(Str(j - 1)) & ")"
WS.Cells(i, 10) = "=sum(j" & LTrim(Str(i + 1)) & ":j" & LTrim(Str(j - 1)) & ")"
WS.Cells(i, 11) = "=count(k" & LTrim(Str(i + 1)) & ":k" & LTrim(Str(j - 1)) & ")"
' Group the rows of this set
WS.Rows(LTrim(Str(i + 1)) & ":" & LTrim(Str(j - 1))).Group
i = j
Loop

End Sub
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.