Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel sorting problem.

Options
  • 05-05-2003 10:09pm
    #1
    Thorbar
    Registered Users Posts: 1,722 ✭✭✭


    I want to sort a list in excel according to ID numbers which are unique. Unfortunately I have to add letters to some of the numbers so you could have 8a, 8b, 8c and so on. Now when I click on sort descending excel puts these numbers at the very bottom of the page and not bettween 7 and 9. Is there anyway around this problem?


Comments

  • Options
    #2
    Gordon
    Registered Users Posts: 35,524 ✭✭✭✭Gordon


    Could you maybe split the cells and sort the numbered column with the letters column?

    I cant remember the formula though sorry.


  • Options
    #3
    Krusty_Clown
    Registered Users Posts: 19,524 ✭✭✭✭Krusty_Clown


    The problem is that when you append a letter after a number in the cell, it becomes a text cell, instead of a numeric cell... So you are them comparing text and numbers (numers go to the top and text goes to the bottom)...

    One option is to have each ID stored as a text cell (e.g. put a single quotation mark at the start of the numeric cell).. Then you should be able to sort them all in the way you want to (but as text).

    You'll get a sort warning, so choose the option:
    Sort numbers and numbers stored as text seperately
    (just remember to store all numbers as text cells).


  • Options
    #4
    Capt'n Midnight
    Moderators, Recreation & Hobbies Moderators, Science, Health & Environment Moderators, Technology & Internet Moderators Posts: 91,761 Mod ✭✭✭✭Capt'n Midnight


    HEX - enter 0-9,A-F as your digits - in the next column - use Hex2DEC to generate numbers (VALUE() etc.) and sort on this column.

    HEX2DEC gives digits 0-F (no other characters)
    but be warned numbers with more digits are bigger,
    ie. 8a > 80 > 9 >8 - Might be possible to use decimal points but not sure ...)

    Tools - Addins - Analysis thingy (also has the mortgague calculater in it)
    "If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu."

    You could use two columns and
    sort by Number first (a1 = 5)
    and then Letter (b1 = a)
    and use a third column to display the concatonated
    =A1&B1 (C1 = 5a)

    If you need higher then F you are in to formulaes to generate numbers another way - by taking off the last char if it is a letter .

    Column Formulae
    A 5a
    B =len(a1)
    C =code(upper(right(a1,1))) 'ie a--> A --> 65
    D = Left(a1,Len(a1)-1) (ie ignore last digit)

    Use the if function to work out if A is between 0 and 9 and then produce a value to sort on

    no letter =value(a1)*256 or
    Letter =Value(left(a1,len(a1)-1) ) * 256 + code(upper(right(a1,1)))


    Ie. no easy / neat way ...


Advertisement