Grouping a WBS in Excel Automatically - Can it be done?

[Amber_Rose92]

Hi All,

This is for Excel 2016 - we haven't yet upgraded to 365.

I need some help with grouping rows of data in Excel for a WBS I am working on.

The WBS spreadsheet has multiple columns with one column being titled "WBS Level" where I have manually entered in what level the data matches to. We count the characters in between the dots as our levels - IE; TST.XXX is considered level 2 with the Project Parent Level (Top Title) considered level one (TST by itself).

Our data goes down to level 8, with 685 rows of data in total. Some do not go all the way down to level 8, some are just parent levels etc. I already have the levels assigned, I just need to group them.

Manually grouping is a nightmare and time consuming, I find that if you try to group within a parent multiple times (ie; you have a level 3 through to 8 in one parent), it breaks and can't group properly. When I filter by level and highlight all rows and click group - it has grouped all of the levels underneath as well. I want to be able to click the 1 through to 8 little quick buttons next to the sheet columns.

Our company has macros and other developer tools locked down for security purposes - meaning I've been trying to either do it via a function/formula, or manually with little success. Ideally we wouldn't use Excel for WBS, but that currently isn't an option.

An example of what one area looks like:

Scope	WBS Level	WBS ID	WBS Element Title
B1	2	TST.XXX	Build Scope Target Cost Estimates
B1	3	TST.XXX.01	Build Scope - XXX 01
B1	4	TST.XXX.01.00	XXX 01: Milestones and Events
B1	5	TST.XXX.01.00.01	XXX 01: Milestones and Events - Key Milestones
B1	5	TST.XXX.01.00.02	XXX 01: Milestones and Events - Project Milestones
B1	6	TST.XXX.01.00.02.01	XXX 01: Milestones and Events - 1
B1	6	TST.XXX.01.00.02.02	XXX 01: Milestones and Events - 2
B1	6	TST.XXX.01.00.02.03	XXX 01: Milestones and Events - 3
B1	6	TST.XXX.01.00.02.04	XXX 01: Milestones and Events - 4
B1	6	TST.XXX.01.00.02.05	XXX 01: Milestones and Events - 5
B1	6	TST.XXX.01.00.02.06	XXX 01: Milestones and Events - 6

This continues for the entire WBS. Can I be saved? Can I do an automatic grouping, or am I limited in my ability to group properly by level and need to use Data filtering as the fix?

I had posted this already in answers.microsoft.com with no success.

Thanks gang!

[#2]

@Amber_Rose92 

First let me just make the observation that you seem to be assuming everybody here not only knows Excel, but also knows what WBS represents. I'd never heard of it. Fortunately for you, a quick Google search let me know the answer [for the rest who might be as uninformed as I was, "WBS" refers to the notion of "Work Breakdown Structure" and is employed in connection with Project Management].

That said, when I copied your sample to Excel and used simple sort on the WBS ID column, Excel sorted them all in order (tested by reversing it).... which leads me to think that if you had others that were, say TXT.YYY.01. etc etc they would still all be sorted (and therefore grouped) in their correct "families."

So, I'm sure I'm missing something here, and it may have to do with not understanding how WBS works, but even though I know what the words "breaks" and "can't group" mean, I don't know what you mean when you say "I find that if you try to group within a parent multiple times (ie; you have a level 3 through to 8 in one parent), it breaks and can't group properly." And I don't think your sample data set is complex enough to illustrate what you mean.

Is it possible to create a more fully representative set? And, ideally, if it is possible, could you post such a sample set as an Excel file? If your work permits such, post that sample on OneDrive or GoogleDrive with a link pasted here that grants access.  Feel free to use the attached as a starter.

[#3]

Can I do an automatic grouping？
What is your expexted result?

like below give parent item and search all children and grand children items of a BOM?
https://club.excelhome.net/forum.php?mod=viewthread&tid=1668411&mobile=