Sort numbers in numerical order

  • Hi,


    I have numbers in a single cell for example 10, 3, 5, 3, 45, 12, 16, 105, 107, 12


    I would like a VBA code to sort these numbers so they will look like so 3, 5, 10, 12, 16, 45, 105, 107. Removing duplicates and sorting in order.


    Any help on tjis would be greatly appreciated as I can not find any solutions on the internet


    Thanks

  • Using Power Query which is available for Excel 2010 and later on Windows Systems. Here is the Mcode


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10"}),
    4. #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {}, "Attribute", "Value"),
    5. #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", Int64.Type}}),
    6. #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Value", Order.Ascending}}),
    7. #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Value"}),
    8. #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Attribute"})
    9. in
    10. #"Removed Columns"


    Attached is the file so you can see all the steps.

  • Alternatively, here is a straight VBA solution

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks for your reply's, greatly appreciated. Your codes work perfectly but another thing I should've mentioned is sometimes the values will be numbers only and other times it will have have numbers and letters mixed for example A100, A101, A105, A104, A102, A87, A101. How could i adjust your code so the outcome will be A87, A100, A101, A102, A104, A105. I need it to work with both numbers only and numbers and letters. If there is a way to do this I would be extremely grateful. Sorry I never mentioned this before.

  • It should be possible. Need a few more details about the numbers with text.

    • Is there only ever a single letter (or no letter) and is that single letter always at the start?
    • Can the cell contain both numbers and Text+number, or just numbers or just Text+number?
    • If both present then how do you want the values to be sorted?
    • For example: 10, 4, A55, C6, 77, B33 how should that be sorted
    • 4, 10, 77, A55, B33, C6 or
    • A55, B33, C6, 4, 10, 77 or
    • 4, C6, 10, B33, A55, 77 (sorted by number irrespective of any text)
    • or something else?

    If there can be more than one letter and the letter(s) could be anywhere within the digits then it begins to get very complicated!

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks for your reply


    I will have 3 different possibilities


    - Numbers, example 95, 100, 101, 101, 103, 103b, 102, 103c, 104, 102 which should be ordered 95, 100, 101, 102, 103, 103b 103c, 104. Numbers here will always be at the start and some with letters after. Remove duplicates.


    - Letters and number, example ARD100, ARD101, ARD101b, ARD100, ARD102, ARD95, ARD107a which should be ordered ARD95, ARD100, ARD101, ARD101b, ARD102, ARD107a. Letters at the start will be the same followed by numbers and some with letters after. Remove duplicates


    - Letters and number separated, example ARD-100, ARD-101, ARD-101b, ARD-100, ARD-102, ARD-95, ARD-107a which should be ordered ARD-95, ARD-100, ARD-101, ARD-101b, ARD-102, ARD-107a. Letters at the start will be the same followed by a dash then numbers and some with letters after. Remove duplicates.


    Hope this helps and is clear. If you could achieve this in one code that would be brilliant but maybe it needs a few separate codes which I could run depending on the type of values?


    Thanks again for your help, much appreciated.