Automate grouping in Excel - macro

I have several thousand lines of data describing a taxonomy.
Each layer in the taxonomy takes up 3 columns. So Layer 1 is A-C, Layer 2 is D-F etc. Currently the taxonomy is 11 layers deep (33 columns)
For each layer the first column is the index, the second is the name, the third is the long description
Each row includes all elements of the hierarchy for that entity.
For example:
Lines 1-50 might all have identical text in columns A-C: "1;birds;creatures that fly and have feathers"
Lines 2-20 might have identical text in columns D-F: "(a);small birds;creatures weighing less than 100g"
lines 3-10 might have identical text in columns G-I: "(i);migratory small birds;small birds that fly more than 100km on a seasonal basis"
and so on.
So Row 3 as a csv would read:
1,birds,creatures that fly and have feathers,(a),small birds,creatures weighing less than 100,(i),migratory small birds,small birds that fly more than 100km on a seasonal basis

I want to run a macro that groups all the Layer 1 rows ending at a change-in-index in column A, then groups all Layer 2 rows ending at a change-in-index in column D and so on for 10 layers.
It should first clear all current grouping
It should always leave 1 line visible and group the rest of that group, so the first row acts as the title to see what has been hidden beneath it
Thank you
LVL 1
hawkeye_zzzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Can you attach a sample workbook? In that workbook please show "before" and "after" sheets.
0
hawkeye_zzzAuthor Commented:
Hi Martin,
Here's an example.
When doing it manually as you can see in the example, Excel has limitations. In this example I would like to have included row 16 in the 3rd level group but couldn't manually. I would also like to have included rows 31 and 32 but can't manually because in both cases those rows are already the bottom row of one or more higher level groups.

So when doing it manually, we may have to 'show' or leave ungrouped the last row, or two rows or more in a group, if it coincides with the bottom row of a higher group. That may not be a limitation when programming it, but it is when grouping manually.
Thanks
Charles
Excel-Hierarchy.xlsx
0
Martin LissOlder than dirtCommented:
I don't understand. Here I selected the 3rd group and row 16 seems to be included.
Cursor_and_Microsoft_Excel.jpg
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

hawkeye_zzzAuthor Commented:
Sorry for the delay getting back to you. I missed the notification email.

Row 16 is visible still, ideally it would be hidden when you group at the third level, bringing the "+" sign for each layer in line.
The third level groups 12-15 with the "+" sign sitting on row 16, So currently 11 and 16 are visible in layer 3 when it is grouped. Ideally I'd like only 11 to be visible. But this is a preference not a necessity.

In other words the way excel works, in the first layer you can group/hide everything but the top line (ideal), in the second layer you can hide/group everything but the top and bottom rows of the rows you want to group/hide, and in the third layer you can hide/group everything but the top and bottom 2 rows. I'd like every layer to be like layer 1, but if it can't be done that's too bad.

Thank you for your time on this.
0
Martin LissOlder than dirtCommented:
I don't believe you can change the behavior of groups from what you describe. Would filtering work for you?
29084247.xlsx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Or, for something completely different, try this workbook that uses a UserForm.
29084247TV.xlsm
0
hawkeye_zzzAuthor Commented:
That userform would be fantastic if it can be automatically created from the taxonomy (using the middle "name" column of each layer), perhaps I could ask another question asking for that? I can't see how you've got the text for the userform you created?

The purpose of the grouping is still important though because we are building a Taxonomy that looks like hitting around 300,000 rows and 13 layers deep, with lots of common sub-trees. For a simple visual check of where we are, the userform is great, but for the purpose of the laborious copy and paste operations of thousand-line sub-trees, I think the grouping (even with the restrictions described above) is the best way to allow us to view-highlight-copy-&-paste those large blocks.

Another great tool would be one allowing to highlight a block, Copy. And paste in the following manner. Using the same reference example above, (excusing the absurdities of the result), highlighting D6:I32. Copy. Select D33. Then the macro performs the following: Insert sufficient rows to paste the block i.e 26 rows inserted after row 33. Paste the block into D33. Copy A33:C33 down to fill the newly inserted lines.

Subject to your response and advice I'll post the first and third tools as separate questions with the grouping still wanted here?
0
Martin LissOlder than dirtCommented:
Unfortunately I don't think that a treeview control (which is what you see on the userform) can handle anything near 300,000 items, so that's out. BTW the text in my sample was hard-coded.

Were you not interested at all in the workbook I attached in post ID: 42476908? The beauty of it is that no coding is really necessary because once you have the filters in row 3 you can copy/paste the data in the "Before" sheet (rows 4 on down) to the "After" sheet. That process could be automated if you want.
0
hawkeye_zzzAuthor Commented:
It wouldn't be 300k items in the treeview, we're looking at anything between 2 and 10 branches at each level, but with 13 levels you end up with around 300k rows altogether. So as long as the treeview code can search for the 'next change' in name at each level the actual items in the treeview would be far less, and could be limited to the top 5 levels for example. This would still be most valuable. What do you think the practical limits are?

Thanks I've had another look at the filtering. Now I appreciate how creative one can be in multi-level filtering that is going to be the most elegant solution, so I'll accept that, but just wait for your thoughts on the treeview and if I should post another question for that, and what parameters I should add?
0
Martin LissOlder than dirtCommented:
Every item in a Treeview is called a node and AFAIK the node limit is < 10,000.
0
hawkeye_zzzAuthor Commented:
Thank you for simplifying the problem. This works well
0
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
hawkeye_zzzAuthor Commented:
Thank you Marty, I'll check that out. Sounds interesting.
I have posted the treeview question just now also
0
hawkeye_zzzAuthor Commented:
Very interesting that one can create one's own formulae - valuable article thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.