excel group and subtotal

Posted on 2012-09-20
Last Modified: 2012-09-21
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?
Question by:joeloyzaga
    LVL 31

    Expert Comment

    by:Rob Henson
    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
    LVL 31

    Expert Comment

    by:Rob Henson
    As attached.

    Rob H
    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
            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

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    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.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now