+ Reply to Thread
Results 1 to 8 of 8

Sorting in Excel

  1. #1
    Registered User
    Join Date
    11-16-2018
    Location
    Egypt
    MS-Off Ver
    MS office 2019
    Posts
    2

    Sorting in Excel

    Hello
    I have departments named such as : One A , One B , Three C , Four E
    I want to sort it automatically to be :One A , One B , One C , One D , Two A , Two B , Two C , Two D , Three A .....Three D , Four A ...... Four E

    How can I do it in Excel not manually ?
    Thanks


  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Sorting in Excel

    Ahlan wa sahlan ramy88!

    Excel does not know that One = 1 and Two = 2 so it cannot sort them. There are three possibilities that I can think of.

    The best solution is to write the departments as 1A, 1B, 3C, 4E.

    The next solution would be to write a formula that converts the words to numbers in another column, then sort using that column.

    The third solution is a macro.

    How high do the numbers go? Is there always one letter?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    11-16-2018
    Location
    Egypt
    MS-Off Ver
    MS office 2019
    Posts
    2

    Re: Sorting in Excel

    Thanks For Your Reply
    Yes there is always one letter
    The department are 6 : One - Two - Three - Four - Five - Six
    Sub-departments are Five : A - B - C - D - E

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sorting in Excel

    then just to follow on with what Jeff wrote, using a helper column put this formula in it...
    =IF(A2="one",1,IF(A2="two",2,IF(A2="three",3,IF(A2="four",4,IF(A2="five",5,IF(A2="six",6,""))))))
    then drag down for the entire length and sort using that. After finished with it you can delete it.
    OR, if you have One A in its own cell you can use this formula then sort by the column you put it in...
    =IF(LEFT(A2,(LEN(A2)-2))="one",1,IF(LEFT(A2,(LEN(A2)-2))="two",2,IF(LEFT(A2,(LEN(A2)-2))="three",3,IF(LEFT(A2,(LEN(A2)-2))="four",4,IF(LEFT(A2,(LEN(A2)-2))="five",5,IF(LEFT(A2,(LEN(A2)-2))="six",6,""))))))&RIGHT(A2,1)
    it will give you 1A and 1B etc and will sort it in numeric ascending order.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Sorting in Excel

    Given that there are a limited number of departments and sub-departments (no more than 30) then another option would, I think, be a custom sort list.

    To create a custom sort list bring up the Sort dialog box, click on the "Order" dropdown and select "Custom list . . .". Click in the "List entries" box and copy/paste the following text, Then click on "Add".

    One A,One B,One C,One D,One E,Two A,Two B,Two C,Two D,Two E,Three A,Three B,Three C,Three D,Three E,Four A,Four B,Four C,Four D,Four E,Five A,Five B,Five C,Five D,Five E,Six A,Six B,Six C,Six D,Six E

    Now whenever you sort you can select this custom list instead of the standard "A to Z"

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Sorting in Excel

    Quote Originally Posted by GeoffW283 View Post
    To create a custom sort list bring up the Sort dialog box, click on the "Order" dropdown and select "Custom list . . .". Click in the "List entries" box and copy/paste the following text, Then click on "Add".
    I use Excel 2010, same as the version shown in your profile, and 2016. I do not see an "Order" dropdown in the Sort dialog box in either version.

    Is this a feature exclusive to 2019, as shown in the OP's profile?

  7. #7
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Sorting in Excel

    I am using Excel 2010. From the point shown in the picture, select "Custom List"

    sort_dialog.png

  8. #8
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Sorting in Excel

    Quote Originally Posted by GeoffW283 View Post
    Given that there are a limited number of departments and sub-departments (no more than 30) then another option would, I think, be a custom sort list.

    To create a custom sort list bring up the Sort dialog box, click on the "Order" dropdown and select "Custom list . . .". Click in the "List entries" box and copy/paste the following text, Then click on "Add".

    One A,One B,One C,One D,One E,Two A,Two B,Two C,Two D,Two E,Three A,Three B,Three C,Three D,Three E,Four A,Four B,Four C,Four D,Four E,Five A,Five B,Five C,Five D,Five E,Six A,Six B,Six C,Six D,Six E

    Now whenever you sort you can select this custom list instead of the standard "A to Z"
    Thank you, GeoffW283.
    I know something new from you

+ 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. [SOLVED] 3-level sorting including sorting a column by the number of percent sign in each cell
    By terryhenderson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-14-2017, 05:21 PM
  2. Replies: 1
    Last Post: 12-16-2015, 01:04 PM
  3. [SOLVED] I require sorting formula in excel sheet with complex sorting.
    By kaminanirav in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2015, 08:16 AM
  4. Replies: 0
    Last Post: 02-24-2014, 11:27 AM
  5. Sorting 2 data ranges by comparing one column in each and sorting to match
    By MDKsmiffy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-17-2013, 03:30 PM
  6. Sorting a range to invert it upside down without sorting by any specific column
    By luv2glyd in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-29-2013, 12:53 PM
  7. [SOLVED] Sorting a range to invert it upside down without sorting by any specific column
    By luv2glyd in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-28-2013, 12:43 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