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

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?
  • 2
1 Solution
Rob HensonIT & 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.

Rob H
Rob HensonIT & Database AssistantCommented:
As attached.

Rob H
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
        j = i + 1
        Do While Cells(j, 5) = "" And Cells(j, 11) <> ""
            j = j + 1
        ' j points to the end of this group
        ' Add the totalling sums
        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
End Sub

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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