Results 1 to 10 of 10
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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.
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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!
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #9
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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