+ Reply to Thread
Results 1 to 10 of 10

Using Format function to fix sorting priorities

  1. #1
    Registered User
    Join Date
    08-11-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    11

    Using Format function to fix sorting priorities

    Forgive me if I'm missing something obvious or making some blatant error, but I'm having a lot of trouble figuring this out.

    So, I've got this table with a numeric component, but I want alphanumeric elements to be properly sorted (1, 1a, 1b, 2, 3, 4a, 4b, et cetera). So after looking into it a bit, I figured reformatting everything as it goes into the table using Format(value,"###") ought to do it. And it does, except that anything with an "a" returns empty. So Format("1b","###") comes back right as does "1c", "1d", et cetera, but Format("1a","###") is returning empty. So I don't really know what to make of it.

    Thoughts?

    Thanks,
    Aule

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Using Format function to fix sorting priorities

    That would be because Format only formats numeric values. You need to check if the value is numeric. If not, just pass the value as is.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    08-11-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    11

    Re: Using Format function to fix sorting priorities

    Ok. Well, I don't know why 1a is non-numeric but 1b is, but thanks. All of the data is going through now, but it isn't sorting properly, so I'll have to figure out another method I suppose.

    Thanks,
    Aule

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Using Format function to fix sorting priorities

    1a is NOT numeric. The attached workbook attempts various checks.

    I have also used
    Please Login or Register  to view this content.
    to see if VBA IsNumeric sees it differently. And it doesn't.


    Guess you need to check your code.


    Regards, TMS
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-11-2015
    Location
    Livermore, CA
    MS-Off Ver
    V.10
    Posts
    1

    Re: Using Format function to fix sorting priorities

    You might try stripping off the alpha character if it exists into a separate column and then sort by column "A" (numeric) and column "B" (alpha).

    Regards, JDT

  6. #6
    Registered User
    Join Date
    08-11-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    11

    Re: Using Format function to fix sorting priorities

    No, IsNumeric doesn't see it differently, but Format does, and I've tested that outside of my code. Format("1a","###") returns "" and Format("1b","###") returns "1b". But at any rate, my problem is just getting the list to sort using 1a, 1b, 2, 3a, 3b et cetera. This was just one method that I read, and is apparently ineffective

  7. #7
    Registered User
    Join Date
    08-11-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    11

    Re: Using Format function to fix sorting priorities

    Thanks, JDT. I considered that, but if at all possible, I want to use the built in sorting on the table. I might have to resort to that method though.
    Thanks again

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Using Format function to fix sorting priorities

    My apologies. You've prompted me to do a little more research and Format is much more sophisticated than I had thought it was. If you click in the middle of the word Format in the VBA Editor and click F1, you'll see the Help for the method.

    You can try:
    Please Login or Register  to view this content.
    However, I'm not 100 sure how that helps because it looks, to me, as though the Format will output the 1 as a number, not a Text Value. So, it seems you would have to make the cell itself Text Format before putting something in it.

    This appears to work:

    Please Login or Register  to view this content.

    Regards, TMS

  9. #9
    Registered User
    Join Date
    08-11-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    11

    Re: Using Format function to fix sorting priorities

    That did the trick! Many thanks, TMS

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Using Format function to fix sorting priorities

    You're welcome. Thanks for the rep.


    You may find that rather than format the alpha-numeric value, you can just copy it, but use the cell formatting and AWF.Text to convert the numeric values. worth playing about, perhaps.

    This would work too:

    Please Login or Register  to view this content.

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] IF Statement with no priorities
    By kraut in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-11-2013, 07:45 PM
  2. Changing priorities in a column
    By phrankndonna in forum Excel General
    Replies: 4
    Last Post: 10-19-2013, 10:44 AM
  3. [SOLVED] How do I set If Statement to set Priorities, off of dates due?
    By K Svoboda in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-16-2013, 08:12 AM
  4. [SOLVED] Calculations based on priorities
    By plank in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-13-2012, 09:13 AM
  5. queuing system with priorities
    By bampoor in forum Excel General
    Replies: 15
    Last Post: 08-03-2010, 09:26 AM
  6. Cell Priorities
    By Latlong in forum Excel General
    Replies: 3
    Last Post: 10-11-2009, 11:40 PM
  7. changing sort priorities
    By chrislcox31 in forum Excel General
    Replies: 1
    Last Post: 07-10-2009, 09:22 PM
  8. new priorities
    By ceemo in forum Excel General
    Replies: 0
    Last Post: 05-04-2006, 12:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1