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]

You need to force the WBS to text, so it sorts correctly. One option:

Insert a column next to the WBS codes. Assuming WBS starts in A2 and B is blank, place this in B2 and fill down: =TEXT(A2,"@")

Sort on column B to get the WBS

Denis

 

[apez]

That will not work.

An Outline/WBS will not sort correctly using any conventional text or numeric sort.

You can test it yourself by pasting the WBS in my original message and trying to sort in Excel.

It requires a sort routine - I am looking for a VBA sort function.

 

[SydneyGeek]

Sorry, but I disagree. Did you try it?

I took your original outline and tested it before posting the reponse. It works. The function coerces everything to text, not a mixture of text and numbers which is the normal result of formatting the column as text.

Denis

 

[apez]

I did test it - it does NOT work

Using any Excel convention sort will not work - it requires a special VBA sort routine

This was the result of the sort which is NOT correct:

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

 

[SydneyGeek]

Strange, because I got...

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

Looks OK to me. did you sort on A or B?

Denis

 

[apez]

BOTH.

Can you post your Excel file

Because I cannnot duplicate your results.

 

[SydneyGeek]

OK. The layout, incorrectly sorted (Col A) with the Text versions in col B

Excel Workbook
	A	B
1	1	1
2	1.1	1.1
3	1.2	1.2
4	1.3	1.3
5	2	2
6	2.1	2.1
7	2.2	2.2
8	2.3	2.3
9	1.3.1	1.3.1
10	1.3.2	1.3.2
11	2.2.1	2.2.1
12	2.2.2	2.2.2
Sheet2

This is the result of sorting on column B

Excel Workbook
	A	B
1	1	1
2	1.1	1.1
3	1.2	1.2
4	1.3	1.3
5	1.3.1	1.3.1
6	1.3.2	1.3.2
7	2	2
8	2.1	2.1
9	2.2	2.2
10	2.2.1	2.2.1
11	2.2.2	2.2.2
12	2.3	2.3
Sheet2

Does that help? BTW, I have tested in EXCEL 2003 and 2007.

Denis

 

[apez]

I have an Excel spreadsheet set up exactly as you have described.

I cannot duplicate your results.

I am not an excel novice so I can assure you that I am struggling to duplicate your solution.


Is there anyway to post the actual file on this message board?

 

[apez]

SEE NEXT POST