Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post "Smart" Sorting and Ordering of Rows in Excel

    Hi,


    I am playing around with some data in my spreadsheets, regarding some kind of "smart" sorting and ordering of rows.

    Here is the raw data which is sorted by Date:

    Name Type Date
    Debit A 1 Jan 2014
    Others B 1 Jan 2014
    Credit A 1 Jan 2014
    Transfer B 1 Jan 2014
    MSC B 1 Jan 2014
    Others B 2 Jan 2014
    Debit A 2 Jan 2014
    Credit A 2 Jan 2014
    Transfer B 2 Jan 2014
    MSC B 2 Jan 2014
    Transfer B 3 Jan 2014
    Others B 3 Jan 2014
    Credit A 3 Jan 2014
    Debit A 3 Jan 2014
    MSC B 3 Jan 2014

    What I need is on top of sorting by Date, I want to sort the rows by Name as well. so for all rows within the same Date, I want "Credit" to always come first and "Debit" to always come the last. For other rows, just leave it as is or whatsoever lol. The expected result should be:

    Name Type Date
    Credit A 1 Jan 2014
    Others B 1 Jan 2014
    MSC B 1 Jan 2014
    Transfer B 1 Jan 2014
    Debit A 1 Jan 2014
    Credit A 2 Jan 2014
    MSC B 2 Jan 2014
    Others B 2 Jan 2014
    Transfer B 2 Jan 2014
    Debit A 2 Jan 2014
    Credit A 3 Jan 2014
    Others B 3 Jan 2014
    MSC B 3 Jan 2014
    Transfer B 3 Jan 2014
    Debit A 3 Jan 2014

    Is there some smart way to sort the rows as expected? Is there some Macro in Excel or Access that can acheive this?

    Thank you so much guys. Would appreciate so much!!!


    Cheers,
    Edmund

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Edmund,

    Welcome to the Lounge as a New Poster!

    What you need is a Custom Sort Order.

    Use the link above for getting to the point where you can...
    CreateCustomSortOrder.JPG
    select Custom List...

    Create the Custom Order:
    CustomSortOrder.JPG

    Now you can use that order to sort the Name column. Note: you didn't specify the order for the items other than Credit First and Debit Last so I took a guess. You can fix this when you create the custom sort list.

    Here's the results. I hope this is what you were after.
    EHResults.JPG

    HTH
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Ed,

    Here would be the macro version that would automate RG's process:

    Code:
    Sub CustomSort()
    '-----------------------------------------
    'FIRST SORT COL C (DATE)- ADJUST TO DESIRED RANGE IF NEEDED
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C3:C17")
    '-----------------------------------------
    'SECOND SORT COL A (NAME)- ADJUST TO DESIRED RANGE IF NEEDED
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A3:A17"), _
             CustomOrder:="Credit, MSC, Others, Transfer ,Debit"
    '-----------------------------------------
    'APPLY SORT- ADJUST TO ENTIRE DESIRED RANGE IF NEEDED
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("A2:C17")
            .Header = xlYes
            .Apply
        End With
    End Sub
    Change Sheet1 to the name of your sheet. Adjust the Order of the Custom List if needed:
    "Credit, MSC, Others, Transfer ,Debit"

    unsorted.png sorted.png
    Attached Files Attached Files
    Last edited by Maudibe; 2014-10-12 at 21:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •