Sorting an Outline/WBS

[apez]

Does anyone have an Excel VBA function to sort an Outline/WBS?

I have a column in an excel workbook with a Outline/WBS.
If a user uses the default Excel sort I need to sort the Outline/WBS back to the correct Outline structure (example below):


1
1.1
1.2
1.3
1.3.1
1.3.2
2
2.1
2.2
2.2.1
2.2.2
2.3

 

[SydneyGeek]

Send me a PM with your email address. I will send you the file.

Denis

 

[apez]

CORRECTION: I was able to duplicate your file and results - It is not necessay to post the file - I was able to duplicate the sort using: Excel toolbar > Data > Sort Column B > Sort numbers and numbers stored as text separately

HOWEVER when I expand the WBS to the following structure:

1
1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
1.10
1.11
1.12
1.12.1
1.12.2
1.12.3
1.13
1.14
2
2.1
2.2
2.3
2.4


I Get the following INCORRECT results including the truncation of '1.10' to '1.1' :

1
1.1
1.1
1.11
1.12
1.12.1
1.12.2
1.12.3
1.13
1.14
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
2
2.1
2.2
2.3
2.4


ALSO, if i sort column A (formatted as text) without using your formula in column B I get the following INCORRECT structure:

WBS
1
2
3
4
5
6
7
1.1
1.10
1.11
1.12
1.12.1
1.12.2
1.12.3
1.13
1.14
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
2.1
2.2
2.3
2.4

 

[SydneyGeek]

OK, everything else works from what I gather. Or is there still something I missed? BTW, I just used the toolbar icons (AZ and ZA)

This problem:

I Get the following INCORRECT results including the truncation of '1.10' to '1.1' :

Is caused by the fact that Excel regards 1.10 as the numeric value 1.1, so you need to store all your values as TEXT. The workaround for me was to write the number out as
1.10. (note the second decimal -- Excel won't try to convert it to a number)
instead of
1.10

Denis

 

[rmweaver81]

Denis,

I know you posted this years ago, but I'm using Excel 2010 and I can't get this to work for me either. I have column A formatted as text with the numbers written as 1.1., 1.10., etc. When I use the toolbar icons, it still puts 1.10. before 1.2. Any recommendations would be greatly appreciated.

Rob