Results 1 to 12 of 12

Sort numbers with multiple decimal points [WBS numbers]

Threaded View

  1. #1
    Registered User
    Join Date
    11-25-2014
    Location
    Swindon, England
    MS-Off Ver
    2013
    Posts
    30

    Sort numbers with multiple decimal points [WBS numbers]

    Hi there everyone.
    I currently have a list of records on a spreadsheet in the following format.

    WBS Title Info
    1 Make Cup of tea The action of making a cup of tea
    1.1 Check ingredients are available Check cupboard, fridge and pantry.
    1.1.1 Check for milk
    1.1.2 Check for sugar
    1.1.3 Check for teabags
    1.2 Test kettle
    1.3 Fetch correct mug Mug preference sheet on the back of door
    etc.


    The structure has 6 levels all the way down to #.#.#.#.#.# and there are sometimes more than 10 levels in each part of the hierarchy (1.14.1.12 is not uncommon).

    I need
    a way to be able to sort the above list so it is in the above order (currently the list is all over the place). Other fixes online explain that splitting the columns using text to columns and then doing a multiple sort will fix it, however a caveat for this is to replace any blank spaces with a 0. i.e. in the above example it would go from 1.14.1.12 to 01.14.01.12. This fix was used for sorting IP addresses which always have the same amount of hierarchical level, but doesn't apply here.

    This fix works great when adding the zeros is quick, however my spreadsheet is so large it is unfeasible to fill the information in this format.

    I'm thinking that we need some sort of loop VBA macro which sorts based on the first digit, then within those groups sorts on the next digit etc.
    The issue I foresee with this though is the following situation will occur.
    1.1.1.1
    1.1.1.2
    1.1.1
    1.1.2.1
    1.1.2.3
    1.1.2
    1.1
    1.1
    2.1.1.1
    2.1.1.2
    2.1.1.

    So between my first level the groups are ordered, however the logic isnt there to tell the rest of the sorting what to do.

    All help is much appreciated WBS Example.xlsx
    Last edited by 6StringJazzer; 03-20-2017 at 08:57 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How do you sort item numbers targeting only specific numbers?
    By matt323 in forum Excel General
    Replies: 10
    Last Post: 12-29-2013, 01:15 AM
  2. Replies: 1
    Last Post: 12-28-2013, 11:46 PM
  3. [SOLVED] sort using vba want to sort text-as-numbers
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-26-2013, 11:46 AM
  4. sort numbers in numeric form in a cell and down the rows
    By system in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-09-2012, 12:15 AM
  5. [SOLVED] How to merge a numbers from 3 cells, eliminate repetitive numbers, and sort such numbers?
    By david gonzalez in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-22-2012, 11:59 AM

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