Results 1 to 7 of 7

Thread: Sort (2003)

  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort (2003)

    Hi,

    We always receive excel files includes medical record number. We would like sort by terminal digit order. However, since some medical record number missing leading zero. So I would to create a button with vb that user can just click a button and then codes will runs to fill the missing leading zero if MRN is less than eight digit, and then sort all medical record by terminal digit order.

    Thanks in advance.

    Regards,

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort (2003)

    Hi Gary

    I may have missed the point but could you format the range with a custom format with 00000000 so that any so a 7 digit number becomes 01234567.
    Jerry

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort (2003)

    Yes, I can. But the terminal digit order is mean:

    01234567

    First sort digit: 5 & 6
    Second sort digit: 3 & 4
    Third Sort digits: 0,1 & 2
    Last sort digits: 7

    Sorry for unclear information.

    Thanks again.

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

    Re: Sort (2003)

    How would adding a leading zero help with sorting the way you indicate?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort (2003)

    leading zero really not help with sorting the way I want, but some time we receive some data doesn't have zero. That's why I would like to add leading zero first if there are missing.

    Thanks

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

    Re: Sort (2003)

    I don't understand why you bring the sort order into this. It doesn't seem to have any relation with your question. Jezza has already told you how you can display a leading zero.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort (2003)

    Create a TDO column with the formula (presuming that A1 has the number, change as desired):

    =MID(RIGHT(REPT("0",8)&A1,8),6,2)&MID(RIGHT(REPT(" 0",8)&A1,8),4,2)&MID(RIGHT(REPT("0",8)&A1,8),1,3)& RIGHT(A1,1)

    Copy this down the column...

    Then you can sort on this column instead of your number

    Steve

Posting Permissions

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