+ Reply to Thread
Results 1 to 6 of 6

Sort issue with 1,1a,2,2a,2b etc

  1. #1
    Registered User
    Join Date
    06-10-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    13

    Sort issue with 1,1a,2,2a,2b etc

    Hello,
    i did post on another forum but the results didnt work and i could post file, so hoping i am hoping i can post file here and someone can fix issue.

    I am trying to sort my rows in numerical order but 4 columns marked Aisle bay shelf Bin in my bin column i have numbers like 1a 1b 1c , when i sort it drops them to the end of rows i want them all in order.

    i have formulas in those cells that need to stay as it reads from different columns.

    Does anyone know how this can be done i have attached the file
    Hope this works.

    Darren
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,895

    Re: Sort issue with 1,1a,2,2a,2b etc

    That is rather confusing. A lot of stuff and not much explanation, and no expected results. A bit of a guess, then...

    In K2, copied across and down:

    =TRIM(MID(SUBSTITUTE("_"&$D2,"_",REPT(" ",125)),125*COLUMNS($A:A),125))

    Then Data/Sort first level by column K, 2nd level by column L, 3rd by M and 4th by N.... Is this what you wanted??
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    06-10-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    13

    Re: Sort issue with 1,1a,2,2a,2b etc

    Hello
    Yes that is very close to what i am after, separating the one column, then sort the columns into order ,
    but as you see when the numbers have an alpha numeric with it it does not go in order i want it to read
    1
    1a
    1b
    1c
    2
    2a
    3
    3a
    3b
    4
    it only puts the at the end of the columns can it be done like this.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Sort issue with 1,1a,2,2a,2b etc

    This proposed solution employs a helper column, O in the attached copy of the file, which may be moved for aesthetic purposes.
    The helper column is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The helper column is then sorted from smallest to largest.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,895

    Re: Sort issue with 1,1a,2,2a,2b etc

    Same basic idea as Jet Mc... but getting rid of a lot of your (Daza's) helper columns
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-10-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    13

    Re: Sort issue with 1,1a,2,2a,2b etc

    thanks will try both formulas on my file will keep you posted

+ 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. sort issue
    By d102macc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-11-2016, 11:01 PM
  2. [SOLVED] Excel 2016 Sort routine issue - recording macro has sheet name in sort routine
    By pongmeister in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-05-2016, 02:45 PM
  3. Sort Issue
    By MKEXL in forum Excel General
    Replies: 2
    Last Post: 04-10-2015, 02:23 PM
  4. Sort issue
    By tony359 in forum Excel General
    Replies: 2
    Last Post: 08-14-2014, 04:03 PM
  5. Sort Issue
    By tbobo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2006, 02:01 PM
  6. sort issue
    By Shawn in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-15-2005, 05:05 PM
  7. Sort Issue
    By GerbilGod7 in forum Excel General
    Replies: 4
    Last Post: 07-29-2005, 06:05 PM

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