PIVOT TABLE – CUSTOM SORT (2000 SR1)

Topic

New Reply

… 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

Reply | Quote

Replies

Is the following too naive?

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

Reply | Quote

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.

Reply | Quote

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.

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

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

Reply | Quote

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

Reply | Quote

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 for everyone.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

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

Reply | Quote