+ Reply to Thread
Results 1 to 7 of 7

How to Correctly Sort Irregular Column

  1. #1
    Registered User
    Join Date
    11-03-2011
    Location
    Venice FL
    MS-Off Ver
    Excel 2003
    Posts
    6

    How to Correctly Sort Irregular Column

    I am having a problem sorting an irregular column.

    I am using Excel 2003. There is a header row

    My column is as follows (not incl the header row)

    LX1
    LX10
    LX21
    LX27
    LX33
    LX34
    LX35
    LX4
    LX6

    I sorted using the cells and as you can see, it sorted it incorrectly.

    Should be

    LX1
    LX4
    LX6
    LX10
    etc

    I tried
    LX-1
    LX-10
    LX-4

    but that made no difference.

    I believe that I have to define the cells in the column as custom but I am unsure how to do this so the sort will be done correctly. By the way, there are even occasionally going to be some entries in the column such as LX10a or LX10Aa. The main thing is to get the sort generally correct and I can put up if these rare extra suffixes are not sorted correctly.

    I am relative new to Excel. Thanks for assistance! Jim
    Last edited by Diesel10; 11-03-2011 at 12:15 PM.

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to Correctly Sort Irregular Column

    One way.

    With your data in A2 down
    In B2
    Please Login or Register  to view this content.
    Then sort on Column B

    If the number in your string can exceed 99 then increase the number of zeros in the TEXT() arguement. e.g. "000" will handle up to 999.

    Hope this helps.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Registered User
    Join Date
    11-03-2011
    Location
    Venice FL
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to Correctly Sort Irregular Column

    Quote Originally Posted by Marcol View Post
    One way.

    With your data in A2 down
    In B2
    Please Login or Register  to view this content.
    Then sort on Column B

    If the number in your string can exceed 99 then increase the number of zeros in the TEXT() arguement. e.g. "000" will handle up to 999.

    Hope this helps.
    I tried Marcol but I must be doing something wrong. With my data starting on row 2 column A (row 1 is the headers), If I put the formula in B2, won't that delete the value in that cell? Anyway, I pasted that formula into B2 cell and sorted and it didn't seem to make any difference. I think you are assuming I know a lot about Excel, but unfortunately I don't. Please spell it out a bit more clearly. Thanks so much for your help!


    Here is a complete copy of the rows and columns to be sorted :
    (It is my stamp collection). First sort by column A and then by Column B
    State Cat #
    AK L19
    AK L20b
    AK W5
    AK W5
    AZ B13
    AZ B19a
    AZ B21
    AZ B23
    AZ B24
    AZ B26
    AZ B28
    AZ B29
    AZ B4
    AZ B6
    AZ B8
    AZ FE1
    AZ FE4
    AZ FE5
    AZ FE6
    AZ FE7
    AZ LX1
    AZ LX10
    AZ LX21
    AZ LX27
    AZ LX33
    AZ LX34
    AZ LX35
    AZ LX4
    AZ LX6
    Last edited by Diesel10; 11-03-2011 at 01:35 PM.

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to Correctly Sort Irregular Column

    Please don't quote whole posts in your replies, the moderators don't like it ...

    You would be best to post a sample of your workbook so we can see where to put the helper column, and not have to guess as to what your typical data looks like.

    Did you download and try the workbook I posted in Post #2?

    From Forum FAQs
    How do I attach a file to a post?

    To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

    On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

    To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

    Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

  5. #5
    Registered User
    Join Date
    11-03-2011
    Location
    Venice FL
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to Correctly Sort Irregular Column

    I solved the problem by manually changing LX4 to LX004 and LX10 to LX010 etc. I need three digits because some catalog numbers are 100 and over.

    Doesn't look nearly as nice but at least the stamps are sorted in the correct order.

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to Correctly Sort Irregular Column

    The formula I gave you does what you did manually!

    Just put it in a spare column drag it down and then copy and paste values to Column A if that is what you are happly with.

    Please Login or Register  to view this content.
    will handle numbers up to 999.

  7. #7
    Registered User
    Join Date
    11-03-2011
    Location
    Venice FL
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to Correctly Sort Irregular Column

    You are very kind to help me. I only had 30 stamps enteredt so far so it wasn't difficult to manually change them and from here on out, I'm with the program in entering them that way...

    Thanks again so much! It was your example that showed me the placeholder concept. Bravo !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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