Link to home
Start Free TrialLog in
Avatar of Charles
CharlesFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Can you attach a sample workbook? In that workbook please show "before" and "after" sheets.
Avatar of Charles

ASKER

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
I don't understand. Here I selected the 3rd group and row 16 seems to be included.
User generated image
Avatar of Charles

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or, for something completely different, try this workbook that uses a UserForm.
29084247TV.xlsm
Avatar of Charles

ASKER

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?
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.
Avatar of Charles

ASKER

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?
Every item in a Treeview is called a node and AFAIK the node limit is < 10,000.
Avatar of Charles

ASKER

Thank you for simplifying the problem. This works well
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
Avatar of Charles

ASKER

Thank you Marty, I'll check that out. Sounds interesting.
I have posted the treeview question just now also
Avatar of Charles

ASKER

Very interesting that one can create one's own formulae - valuable article thanks