Sorting an Outline in form #.#.#

[Guggymom]

Hello. I’ve got a tough sorting problem I can’t seem to find an answer to. I'm trying to sort numbers from an outline where the numbers work as follows:

1.1.0, 1.1.1, . . . . 1.1.9, 1.1.10,
1.2.0, 1.2.1, 1.2.2 . . . . 1.2.10, 1.2.11, 1.2.12
1.100.0, 1.200.0, 1.300.0, 1.400.0
2.1.0, 2.1.1, 2.1.2 . . . . 2.1.7
2.100.0, 2.200.0, 2.300.0, 2.400.0
etc.

Every #.# sequence is variable in how high the other numbers go (but always in sequence). So the 1.1.0 series might end at 1.1.10 but the 1.2.0 series might end at 1.2.12 and the 2.1.0 series ends at 2.1.7. in addition, initial number also has a series that goes 1.100.0, 1.200.0, 1.300.0, 1.400.0 (this was done because these four items are always the same, so that way we could be assured they'd always have the same number, no matter how many other items were in the series.
I want to sort it as it appears above, as Word would do it in outline form, but Excel always sorts like a dictionary, treating the .11 as after .1, rather than as a number where .11 comes after. 10 (i.e., sorts them as 1.1.0, 1.1.1, 1.1.10, 1.11...1.1.2, 1.1.3). I have tried changing the format to several different types, including Number, Text, Decimal, and cannot get Excel to sort it in the correct order for an outline. I suspect there is a way to do it with a custom number format, but I really don't know how those work.

Any and all solutions appreciated. Thanks very much!

 

[Sajan]

Hi @guggymom,
Have you considered a Text to Columns split on the "." delimiter, and then doing a custom sort on the three columns? (Doing the Text to Column, you get actual numbers that Excel knows how to sort properly!)

Cheers,
Sajan.

 

[Guggymom]

Yes, that would work, but adds 3 columns (and additional maintenance) to an already big spreadsheet. Since Microsoft offers this outline order in Word, you'd think there would be a way to sort the same way in Excel! I was hoping there was a more elegant solution somewhere.

 

[NARAYANK991]

Hi ,

What you want done becomes simple if each of the fields is the same width e.g. if we can revise 1.1.0 to 001.001.000 , then the sorting would be easy.

This is possible only if you can confirm the maximum number of digits possible in your file.

How to get the text 1.1.0 into the form 001.001.000 is a different matter ; using formulae , it is not difficult , but the formulae will be long ; using VBA it is a very simple matter.

Narayan

 

[Guggymom]

Thanks Narayan. Yes, I assume that would work for the sorting, but hard on the humans to process and understand! Any other suggestions?

 

[Sajan]

You could use a helper column that keeps the values as Narayan suggested so that you can sort on it, but humans can read the original column.

 

[NARAYANK991]

Hi ,

I merely pointed out that what you wanted could be achieved using VBA ; does what the VBA code is doing have to be understood by others ? All they will have to do is click a button.

There will be no additions / modifications / deletions in the worksheet ; the final output from the code can either replace the existing data , or it can be entered into any unused column.

Narayan

 

[jeffreyweir]

Late contribution, but if you ensure that the outline column is entered a text (and not general format) AND each sub part of the outline has the same amount of digits in it, then you can sort on it, as outlined at this post:
http://www.mrexcel.com/forum/excel-questions/268099-sorting-outline-wbs.html

I have a spreadsheet where I accomplish this on your sample dataset using 8 additional columns. (I could use less, but it wouldn't be as efficient). But for some reason I can't upload it...I don't think this forum likes files created in Excel 2016 or something, because they just don't show up in the explorer window when I click the Upload File button.

This is a case where VBA would be much simpler, and if the code was well written, much more efficient too probably. Might punch some code up as I need to do this for an app I'm building in Excel.