# Thread: PIVOT TABLE - CUSTOM SORT (2000 SR1)

1. ## PIVOT TABLE - CUSTOM SORT (2000 SR1)

... Looking for ideas

A pivot table has the following WBS ID fields (shown below). If they are sorted ascending, you get the order shown. However, I want the sort order to be C.1, C.2, C.3, ... C.10, C.11.... (C.10 should not come after C.1). I have a work around removing the "dots" and inserting a space and sorting that way, however, want to know if there are simpler ways to do this.

WBS ID
C.1
C.10
C.11
C.12
C.13
C.14
C.2
C.3
C.5
C.6
C.7
C.8
C.9

Thanks

2. ## Re: PIVOT TABLE - CUSTOM SORT (2000 SR1)

Is the following too naive?

Put the numeric values in the source table (1, 10, 11, ...) and format them as "C."0

3. ## Re: PIVOT TABLE - CUSTOM SORT (2000 SR1)

Hans,

... you are everywhere.....

The numeric values is a good idea, however, the WBS items are actually C.1.10.5.22.4.2.3 (etc.) and can go as many as 10 levels deep. (Which, makes your numbering idea somewhat difficult unless you can think of an easy way to number the wbs elements.) Rory gave me the idea to remove the dots and insert spaces, which works, however, tends to create a problem with the pivot table such that if too many columns are selected to pivot on, the sort does not work. (Actually - I get an error message)

Thanks for the idea.

4. ## Re: PIVOT TABLE - CUSTOM SORT (2000 SR1)

You could use a "sorting column" with a formula like this:

=Sortstring(A1)

which will turn the text "C.3.34.1.2" into "C03340102"
and "C.12.1.1" into "C120101", etc.

Never heard of the function Sortstring(cell)? That's cuz I made it up! It's a custom function you can paste into a module in your workbook. It'll pick out the numbers between periods and format them to 2 digits and concatenate them into a string that'll sort as many levels down as you need.

<pre>Function SortString(sInput As String) As String
SortString = Left(sInput, 1)
For x = 3 To Len(sInput)
char = Mid(sInput, x, 1)
If Asc(char) > 47 And Asc(char) < 58 Then
tmpNum = tmpNum + char
Else
SortString = SortString + Format(Val(tmpNum), "00")
tmpNum = ""
End If
Next x
SortString = SortString + Format(Val(tmpNum), "00")
End Function
</pre>

Weird. But it works in a pinch -- like this. Hide the column or delete it when you're done.

5. ## Re: PIVOT TABLE - CUSTOM SORT (2000 SR1)

Hi Gary,

Here is a variation on DoryO's suggestion.

It keeps the periods instead of omitting them, inserts leading 0's if necessary (so that the strings will sort correctly), and uses InStr instead of looking at every character. The code is longer, but executes much faster - especially if there are many levels. Also, it uses & to concatenate strings instead of +.

As in Dory's code, I assumed that the individual numbers are between 0 and 99.

Function SortString(sInput As String) As String
Dim intPos1 As Integer
Dim intPos2 As Integer
Dim sOutput As String
sOutput = Left(sInput, 1)
intPos1 = 2
intPos2 = InStr(intPos1 + 1, sInput, ".")
If intPos2 = 0 Then
intPos2 = Len(sInput) + 1
End If
Do While intPos1 <= Len(sInput)
sOutput = sOutput & "."
If intPos2 - intPos1 = 2 Then
sOutput = sOutput & "0"
End If
sOutput = sOutput & Mid(sInput, intPos1 + 1, intPos2 - intPos1 - 1)
intPos1 = intPos2
intPos2 = InStr(intPos1 + 1, sInput, ".")
If intPos2 = 0 Then
intPos2 = Len(sInput) + 1
End If
Loop
SortString = sOutput
End Function

Insert a column next to the WBS items
Enter the formula =SortString(...) next to the first item
Fill down
Use the new column in your pivot table.

If you can live with C.01.10.05.22.04.02.03 etc., you can replace the formulas by values (Copy, Paste Special, Values) and delete the original column.

Regards,
Hans

6. ## Re: PIVOT TABLE - CUSTOM SORT (2000 SR1)

Thanks to everyone for the suggestions and help. I will need to review these and give them a try and let you know how it works out.

If I can get this to work, a <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> for everyone.

7. ## Re: PIVOT TABLE - CUSTOM SORT (2000 SR1)

Hi Hans, I'm no expert on VB performance so I'm curious about how your version runs faster. What differences improve the performance? Maybe I need to give some of my production code a makeover, too!

8. ## Re: PIVOT TABLE - CUSTOM SORT (2000 SR1)

Hello Dory,

Your version uses a For...Next loop to look at every character in the string. My version uses the InStr function to jump from one period "." in the string to the next. But that probably doesn't make a whole lot of difference. But I have noticed in the past that the Format function is relatively slow.

In practice, you'll hardly notice the difference when you apply your or my function to a reasonably sized list of entries. For 100 entries, execution time is negligible. Testing 10,000 times, your function took 3 seconds and mine 0.5 seconds on my 400 MHz Pentium II; on newer machines you'd probably have to test 100,000 or 1,000,000 times.

Regards,
Hans

9. ## Re: PIVOT TABLE - CUSTOM SORT (2000 SR1)

Gary, I have struck this very problem in a WBS Structures in Excel and other applications.

My simple solution was to make each level 2 digits
eg C.01; C.02;C.01.23.09.01 etc
Sort now gives me what I want
HTH

10. ## Re: PIVOT TABLE - CUSTOM SORT (2000 SR1)

Thanks for the info. I have tried to go that route, however, the users indicate that they do not want to change the appearance of the WBS. <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>

I now pass something similar to what you suggested as an alternate sort field.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•