Results 1 to 6 of 6
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Sorting within a cell (Excel 2000)

    Hi All,

    I have a column of data containing numeric codes.
    1. Each cell within the column may contain a single code, none at all, or several (each separated by a comma).
    2. The codes are 8 digits (but may start with leading zeros)
    3. Some of the codes may be preceded with a minus sign.

    I want to sort the entries in each cell in a particular kind of order:
    For example, if the cell contains
    11222222, 33222222, 22222222
    I want to replace it with
    11222222, 22222222, 33222222

    if the cell contains
    44222222, -98765432, -12345678, 23456789, 00123456
    I want to replace it with
    -12345678, -98765432, 00123456, 23456789, 44222222
    i.e. all the minus values at the beginning but sorted in ascending value as if there were no minus sign, followed by all the other values in asceending order.

    I thought of using text-to-columns for each cell, sorting, and then 'joining' up the values again.
    But I lose the leading zeros in some of the entries.
    Each cell can have up to 30 or so codes.

    It's doing my head in.

    Can anyone help with routine to read the stuff in (cell-by-cell), sort it, and stick it back out??
    Any suggestions would be very welcome indeed.

    zeddy

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting within a cell (Excel 2000)

    See if the VBA code below will do what you want. The code assumes that the codes are in column A of Sheet1 and that the start in cell A1. If those are not correct, the code will need to be modified.

    <pre>Public Sub SortStrings()
    Dim I As Long, J As Long, K As Long
    Dim strWk As String, strWkA() As String
    For I = 0 To Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    strWk = Trim(Worksheets("Sheet1").Range("A1").Offset(I, 0).Value)
    If Len(strWk) > 0 Then
    If Right(strWk, 1) <> "," Then strWk = strWk & ","
    J = 0
    Do While Len(strWk) > 0
    ReDim Preserve strWkA(0 To J)
    strWkA(J) = Left(strWk, InStr(strWk, ",") - 1)
    J = J + 1
    strWk = Trim(Right(strWk, Len(strWk) - InStr(strWk, ",")))
    Loop
    If UBound(strWkA) > 0 Then
    For K = 0 To UBound(strWkA) - 1
    For J = K + 1 To UBound(strWkA)
    If strWkA(J) < strWkA(K) Then
    strWk = strWkA(K)
    strWkA(K) = strWkA(J)
    strWkA(J) = strWk
    End If
    Next J
    Next K
    strWk = ""
    For J = 0 To UBound(strWkA)
    strWk = strWk & strWkA(J) & ", "
    Next J
    Worksheets("Sheet1").Range("A1").Offset(I, 0).Value = strWk
    End If
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Sorting within a cell (Excel 2000)

    Legare,

    You are THE ONE.

    It works brilliantly - OK, I ran it and it left a trailing comma at the end which shouldn't be too hard to eliminate.
    Top marks!!!!

    zeddy

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting within a cell (Excel 2000)

    OOPS! I meant to fix that trailing comma and space before I uploaded it. Try this:

    <pre>Public Sub SortStrings()
    Dim I As Long, J As Long, K As Long
    Dim strWk As String, strWkA() As String
    For I = 1 To Worksheets("sample").Range("A65536").End(xlUp).Row - 1
    strWk = Trim(Worksheets("sample").Range("A1").Offset(I, 0).Value)
    If Len(strWk) > 0 Then
    If Right(strWk, 1) <> "," Then strWk = strWk & ","
    J = 0
    Do While Len(strWk) > 0
    ReDim Preserve strWkA(0 To J)
    strWkA(J) = Left(strWk, InStr(strWk, ",") - 1)
    J = J + 1
    strWk = Trim(Right(strWk, Len(strWk) - InStr(strWk, ",")))
    Loop
    If UBound(strWkA) > 0 Then
    For K = 0 To UBound(strWkA) - 1
    For J = K + 1 To UBound(strWkA)
    If strWkA(J) < strWkA(K) Then
    strWk = strWkA(K)
    strWkA(K) = strWkA(J)
    strWkA(J) = strWk
    End If
    Next J
    Next K
    strWk = ""
    For J = 0 To UBound(strWkA)
    strWk = strWk & strWkA(J) & ", "
    Next J
    strWk = Trim(Left(strWk, Len(strWk) - 2))
    Worksheets("sample").Range("A1").Offset(I, 0).Value = strWk
    End If
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Sorting within a cell (Excel 2000)

    Legare

    Tried it out - it's magic.
    I can't thank you enough!
    You have saved me so much time.
    When it comes to loops, you certainly know your stuff!!
    It is very fast too! Blink of an eye!
    I am very impressed.

    Many thanks again!

    zeddy

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting within a cell (Excel 2000)

    Yea, I'm definitely loopy.
    Legare Coleman

Posting Permissions

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