+ Reply to Thread
Results 1 to 6 of 6

Sort Question - sort on 2 columns, but only if value exists in 3rd column

  1. #1
    Registered User
    Join Date
    04-19-2004
    Posts
    5

    Sort Question - sort on 2 columns, but only if value exists in 3rd column

    I want to be able to sort by two columns, but only if a 3rd column has a value in that row.

    I.e. Column A is the price, Column B is the date, and Column C is the quantity.

    I need to sort by Price, then Date, but ONLY if there is a value on that row in the Quantity column. Any rows with no value or where value = 0 in the Quantity column can sort below those with values and be ignored. Essentially, I want to see prices and dates for anything that has a current quantity (in stock).

    Thanks for any help.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,202

    Re: Sort Question - sort on 2 columns, but only if value exists in 3rd column

    Hi and welcome to the forum

    I would suggest using a helper column to determine if QTY has a value, something like...
    =if(C1>0,1,0)
    then sort 1st by the helper, then by price and date
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-19-2004
    Posts
    5

    Re: Sort Question - sort on 2 columns, but only if value exists in 3rd column

    Thanks for your suggestion.

    I really don't want to have to add another column to this spreadsheet. It is much more lengthy and complicated than my very abbreviated example, and adding yet another column to the spreadsheet just adds to the complexity of things needing to be remembered each time I work on it. My brain's already overloaded .

    Is there another way to do it?
    Last edited by dmva; 04-25-2013 at 11:04 PM.

  4. #4
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Sort Question - sort on 2 columns, but only if value exists in 3rd column

    Hello,

    You can always add more criteria into the sort function in Excel. For example, you can sort by column C first, then sort by Column A and finally by Column B.

    The downside of this is that it might be a mess, because sorting Column A and B is not prioritized.

    I really suggest you add a helper column like FDibbins said, make / ask for a macro, or ... make a custom auto-sorting sheet using macro / formulas or both. The last option is kinda complicated though ...
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  5. #5
    Registered User
    Join Date
    04-19-2004
    Posts
    5

    Re: Sort Question - sort on 2 columns, but only if value exists in 3rd column

    Thanks for your reply.

    I am really trying to get a list of prices, in order from highest to lowest, sorted by date, with a quantity in stock. My list is 16,000 rows long, but only about 3,000 of those rows have a quantity in stock. I should have been clearer as to what I was trying to accomplish.

    I tried sorting by column C first, but since the quantities vary, that threw off the Price column A, which is where I want to pull data from. I wind up with a batch of prices highest to lowest with quantity of 1, then another batch of highest to lowest with quantity of 2, etc. Obviously, that is not helpful to what I want to use.

    If I had some way to sort by Column C first, but the Sort function could be made "blind" to the actual value existing in rows and act on seeing any value there, that would be the ticket.

    Any other ideas?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,202

    Re: Sort Question - sort on 2 columns, but only if value exists in 3rd column

    OK heres another suggestion.

    Do the sort as you 1st said...A - B - C, then apply filters and filter out all the 0's in C

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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