Solved

# excel group and subtotal

Posted on 2012-09-20
382 Views
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
Question by:joeloyzaga

LVL 31

Expert Comment

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

LVL 31

Expert Comment

As attached.

Rob H
summary.xls
0

LVL 7

Accepted Solution

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

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

#### Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!