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

**dmva** · 04-25-2013, 10:10 PM

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.

**FDibbins** · 04-25-2013, 10:30 PM

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

**dmva** · 04-25-2013, 11:00 PM

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?

**Lemice** · 04-25-2013, 11:11 PM

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 ...

**dmva** · 04-25-2013, 11:26 PM

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?