Can outline groups be made dynamic for sorting?

[aquixano]

I have a sorted worksheet that includes Outline/Grouped rows as well as single rows. As I add new rows and resort the worksheet, the previously grouped row NUMBERS remain grouped, however the rows previously grouped are often shifted out of that group.

When I group rows I add a new row below to total the rows grouped, but after I add new rows and resort (new rows are never added to existing groups), I have to delete existing groups & total rows and manually set up the all the groups again. Is it possible to make existing outline groups dynamic so they follow the same rows when the worksheet is resorted?

I appreciate any suggestions.

 

[Col Delane]

Grouping is not a dynamic feature (unless you insert a row within an existing outline group) like Conditional Formatting - it is more like a flag you set on each row.

The best advice I can give is to set up your database as an Excel Table (so it's dimensions auto-expand as you add data which saves having to create dynamic range-names) and then create a macro to:

1. clear the existing row outline (if any),
2. sort the data as required, &
3. apply a new outline.

You could do this yourself by recording a macro whilst you followed those steps, but would then have to tidy-up the code as Excel records some stuff that's not really useful or flexible.

 

[aquixano]

Thanks for the info and your suggestion Col Delane – it’s disappointing, however not surprising given how little discussion I’ve been able to find on the question.

I think I can write a macro that will delete/recreate the summary rows /groupings with a modifications/identifier on the summary row. I wanted to avoid recreating the wheel if there was some native functionality that I wasn’t finding to make the outline dynamic.

Thanks for your table suggestion – I haven’t used table a lot, however it seems like they really want / work better when the row formulas as the same between rows, which I won’t always have.

 

[Col Delane]

I wouldn't be so disappointed - bear in mind that many of Excel's features and functionality do not do everything users want (peruse the Excel User Voice website (www.excel.uservoice.com) to see the long list of suggestions for improvement that has taken Microsoft 30 years to ask for!! )

Once you have the macro (which I expect to be pretty much a simple capture of the commands you would manually complete) it will be a quick click of a button to refresh your Outline. If you wanted the refresh to occur immediately after each change, you could go one step further and add a Worksheet_Event macro (probably the Change_Event type) to the sheet object in the VBE to automatically execute the macro.

Irrespective of their auto-replication of formulas in columns, Tables just save having to do a couple of other things to make the macro work (e.g. identifying its size/dimensions to enable the macro to reference and manipulate it.)

From my experience, the macro recorder doesn't do that good a job, so you will need to tidy-up the commands, especially to replace "hard coded" range references with the Table and Field names, etc.

Good luck. Post back if you have trouble - that's what the Forum is for.

 

[aquixano]

Thanks, I appreciate that.

Do you know if there is an easy way to identify the last grouped row in a group? The one that remains visible when the groups are collapsed.

 

[Col Delane]

I'm not aware of an easy way - most code that I've seen or written around Outlines requires For Next Loops to cycle through each row to determine its status (e.g. Outlines can be up to 8 levels deep, so you must first identify the level at which the row is grouped.)

Why do you need this? If you go with the 3-stage macro suggestion to remove ALL existing grouping (from the entire database), re-sort ALL the data, and then re-apply grouping (to the entire database), you don't need to care about any particular row or sub-group.

 

[aquixano]

It turned out to be a relatively simple process. I was already doing a three level support with two helper columns, so the order was solid; it was just the outline grouping that was off due to the shifting of the rows. I shaded the visible row of the group, so I simply had to clear the group outline and then, as you suggested, loop through the entire list looking for the shaded row which I recreated the groups. Easy-peasy.

This is similar to your suggestion and to answer your question, I don’t see how putting it in a Table would help / or how I could reapply groupings to the entire database would work (although it’s entirely possible that I’m missing some Table functionality) in my specific situation.

I don’t see a way to mark this thread closed/solved and I didn’t find that in the forum info. Am I supposed to do that and if so, how is it done?

Thanks again for your help.

 

[Col Delane]

1. As mentioned, an Excel (structured) Table automatically keeps track of its own dimensions (unlike a basic contiguous range of cells as we had to deal with before XL2010, where we used Defined Names with formula to scope the boundaries of the target range), which then allows for easy referencing in VBA code.

2. I must also apologise , for whilst I was writing this post I realised I was actually thinking about another great Excel feature that uses grouping - Subtotals!! You may wish to consider this because you can apply it to (or remove it from) the entire database in one go (the primary aspect I was thinking about) - though a separate pass is required for each additional level required. The functionality inserts a subtotal row (and formula using a variety of aggregating functions) on each change in the field being subtotalled, and simultaneously groups the subsets. You can also choose whether to have the subtotals/parent row above or below the children.

So rather than "adjust" subsets of your existing Grouping (and having to loop through individual rows, which is fiddly) whenever you add new rows, what I'm suggesting is that your macro:

1. completely removes all existing grouping (Subtotals) from the entire database
2. re-sorts the entire database as required to enable the grouping required (multiple levels of sorting if necessary to support multiple levels of grouping)
3. re-apply grouping (Subtotals) at the levels required. If I recall correctly (it's a while since I've actually had to use this functionality) you need to apply the highest lowest level subtotals first, and then work down to the lowest.

Check out this video for a good demonstration:
https://www.youtube.com/watch?v=N8WPJ6fBJks

All of this would be just an automation of the steps you'd do manually - but would complete it in the blink of an eye. Remember that Grouping is just an "overlay" that sits on top of your original data - and even though Automatic subtotals does actually insert subtotal rows, it can also remove them to reinstate your data range to its original state.

Note also that whilst Subtotals adds formula to Sum, Count, etc. your subgroups, if you just want the grouping/outline you can easily remove these with a few extra steps:

1. Collapse the outline so just the lowest level (data) rows are hidden, thus displaying ALL subtotal rows
2. Select the entire range
3. Press F5 (Goto) and click the Special button at the bottom left (Note that there is a button you can add to your Quick Access Toolbar which will do this with one click)
4. Click the "Visible cells only" radio button to select it (deselects the 'hidden' cells)
5. Press Delete to clear the formula whilst leaving the outline in place.

3. Re marking a thread closed/solved:
The only thing you can do is edit the Title, say by prefixing the original with "SOLVED:"