+ Reply to Thread
Results 1 to 11 of 11

Excel 2007 : Sorting based on multiple conditions

  1. 02-28-2012, 02:57 PM #1
    hani9041
    hani9041 is offline
    Registered User
    Join Date
    02-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Sorting based on multiple conditions

    Hi, I'm new to this forum (and to VBA in Excel) but I see a lot of smart people who may be able to help me out. I've seen a few solutions similar to my problem on other threads but not exactly what I needed.

    I'm looking for an excel macro that will sort a list set of data based on multiple conditions, for example:

    Column A Column B Column C Column D Column E Column F
    Data_type A Data_type B Data_type C Data_type C Data_type B Data_type A
    Data_type A Data_type B Data_type C Data_type C Data_type B Data_type A
    Data_type A Data_type B Data_type C Data_type C Data_type B Data_type A

    The sorting should happen in 2 phases:
    1) Column A & B & C should be sorted on following criteria
    a) Descending sort of column A
    b) Descending sort of Column C
    2) Column D & E & F should be sorted on following criteria
    a) Descending sort of column F
    b) Ascending sort of Column D

    So the first 3 columns are independent of the second set of 3 columns.

    The tricky part is:
    1) The original data should remain in tact and the newly sorted list should be somewhere else on the spreadsheet
    2) The sorted data should be updated in real-time (as the original data is being updated)

    Thank you,
    +HZ
    Last edited by hani9041; 02-29-2012 at 12:04 PM.
    Reply With QuoteRegister To Reply
  2. 02-28-2012, 03:21 PM #2
    mrice
    mrice is offline
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Sorting based on multiple conditions

    Please have a look at the attached. I've assumed that you have header rows. The code to drive this event driven macro can be found in the Sheet1 tab in the VBA editor (Alt F11)
    Attached Files Attached Files
    Martin
    Reply With QuoteRegister To Reply
  3. 02-28-2012, 04:52 PM #3
    hani9041
    hani9041 is offline
    Registered User
    Join Date
    02-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Sorting based on multiple conditions

    Wow that was quick
    I see the data being moved over to columns J-O but I only see column A & D being sorted and nothing else. I guess what I was looking for was that column A would get sorted along followed by a second condition of sorting Column C (so Columns A-C is one group) and Column F would be sorted and a second condition would be for D to be sorted (so Columns D-F is the second group)

    Please see the spreadsheet for current behavior (sheet 1) and expected behavior (sheet 2); note the expected behavior is worked out manually.

    Thanks,
    +HZ
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 02-28-2012, 04:57 PM #4
    hani9041
    hani9041 is offline
    Registered User
    Join Date
    02-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Sorting based on multiple conditions

    Sorry Martin let me correct myself I see column C (header C) updating correctly along with Column A (header A). What I don't see happening is that Column F (header F) updating correctly.

    Also How would I adept this to my own spread sheet?

    Thanks,
    +HZ
    Reply With QuoteRegister To Reply
  5. 02-28-2012, 05:34 PM #5
    mrice
    mrice is offline
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Sorting based on multiple conditions

    Revised code

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 02-28-2012, 05:55 PM #6
    hani9041
    hani9041 is offline
    Registered User
    Join Date
    02-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Sorting based on multiple conditions

    Martin,

    It looks great, it's exactly what I was looking for. I have 2 minor problems though:
    1) A residual affect of removing data (which is no longer valid) is that it remains sorted in the new table, is there a way to get rid of it?
    2) I'm having problems moving the VBA code into a different spreadsheet, thoughts on this?

    Thanks,
    +HZ
    Reply With QuoteRegister To Reply
  7. 02-28-2012, 05:58 PM #7
    hani9041
    hani9041 is offline
    Registered User
    Join Date
    02-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Sorting based on multiple conditions

    Okay I spoke too soon, it is working when I move it to a different spreadsheet. It seems like the fact that I'm my data is actually links from a different sheet. Is there a way to get it to sort the value of the cell rather then the formula in the cell?

    If not how do I get the script to read specific cells rather then the entire sheet?

    Thanks,
    +HZ
    Reply With QuoteRegister To Reply
  8. 02-28-2012, 06:01 PM #8
    hani9041
    hani9041 is offline
    Registered User
    Join Date
    02-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Sorting based on multiple conditions

    Its a constant range that I'd like to work with. (ie D1:I21)
    Reply With QuoteRegister To Reply
  9. 02-29-2012, 11:19 AM #9
    hani9041
    hani9041 is offline
    Registered User
    Join Date
    02-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Angry Re: Sorting based on multiple conditions

    For some reason the code I have (below) only is triggered when something in the first 7 columns is modified. Also the second set of sort is not working properly, can someone else me:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 03-01-2012, 05:01 AM #10
    mrice
    mrice is offline
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Sorting based on multiple conditions

    You have Order2 specified twice in each line - one of them should be Order3.

    I would expect the code to run when any of the changed cells are in columns 1 to 12 inclusive. I would suggest trying to put in a Stop line as the first line of code below the Sub name so that you can see if the code is running or not.
    Reply With QuoteRegister To Reply
  11. 03-12-2012, 08:33 AM #11
    hani9041
    hani9041 is offline
    Registered User
    Join Date
    02-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Sorting based on multiple conditions

    Yup that fixed my sorting issue. Thank again for all your help.

    +HZ
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1