Hi there everyone.
I currently have a list of records on a spreadsheet in the following format.
WBS Title Info
1 Make Cup of tea The action of making a cup of tea
1.1 Check ingredients are available Check cupboard, fridge and pantry.
1.1.1 Check for milk
1.1.2 Check for sugar
1.1.3 Check for teabags
1.2 Test kettle
1.3 Fetch correct mug Mug preference sheet on the back of door
etc.
The structure has 6 levels all the way down to #.#.#.#.#.# and there are sometimes more than 10 levels in each part of the hierarchy (1.14.1.12 is not uncommon).
I need
a way to be able to sort the above list so it is in the above order (currently the list is all over the place). Other fixes online explain that splitting the columns using text to columns and then doing a multiple sort will fix it, however a caveat for this is to replace any blank spaces with a 0. i.e. in the above example it would go from 1.14.1.12 to 01.14.01.12. This fix was used for sorting IP addresses which always have the same amount of hierarchical level, but doesn't apply here.
This fix works great when adding the zeros is quick, however my spreadsheet is so large it is unfeasible to fill the information in this format.
I'm thinking that we need some sort of loop VBA macro which sorts based on the first digit, then within those groups sorts on the next digit etc.
The issue I foresee with this though is the following situation will occur.
1.1.1.1
1.1.1.2
1.1.1
1.1.2.1
1.1.2.3
1.1.2
1.1
1.1
2.1.1.1
2.1.1.2
2.1.1.
So between my first level the groups are ordered, however the logic isnt there to tell the rest of the sorting what to do.
All help is much appreciatedWBS Example.xlsx
Bookmarks