Sorting/Filtering

[Brandy]

I have a spreadsheet with 8 columns: Item #, Pack, Size, Description, Product
#, Category, Slot, Sales.

I want to sort the entire spreadsheet by Sales, in descending order. That
way it shows my highest sales first. HOWEVER ................ This is my
problem ........ For instance, on my spreadsheet I know that my item named
Gaucho Brown Jersey with a pack size of 1 is my highest selling item at 367.
But there is also another Gaucho Brown Jersey - pack size 24 listed on my
spreadsheet. It only has a Sale # of 73 but I need it to be listed under the
other Gaucho Brown Jersey.

So I really need all the "descriptions" to stay together. But need it listed
with highest sales first, in descending order.

I have tried every sort and filter that I can think of ...

Please help!

 

[Dave Peterson]

I'd add a ninth column that summed all the sales quantities for each
description.

=sumif(d:d,d2,h:h)
(headers in row 1, description in column D and sales amount in column H.)

Then drag down.

Sort your data by the helper column, then the description, then the sales
column.

 

[Brandy]

Well, this helped a lot ... but still gave me a few problems.

For instance: one item category has 4 entries (batteries)... their total
quantity sales adds up to 289. But I have another category that has 2
enteries (chapstick) that adds up to 253.

But my chapstick has a single entry that is 252 in sales and that is higher
than the top selling single entry in batteries of 176. so I actually would
need the chapstick listed before the batteries.

I want it sorted descending by the highest single sales entry in each
category.

I guess there isn't a way to actually post my data for you to see, is there?

Brandy

 

[Dave Peterson]

First, you could use a site like www.savefile.com to share a copy of your
workbook--but lots of people (including me) won't open the workbook.

I think you did a fine job of refining your requirements.

I'd change the helper column formula to something like:

=MAX(IF($D$2:$D$100=$D2,$H$2:$H$100))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

Then sort by the helper (descending) column, the description column and then the
sales (descending) column.

 

[Brandy]

OH!!!!! I think that worked. I think it gave me exactly what I wanted. You
are THE BOMB!!!!!!

 

[Dave Peterson]

Woohoo!!!

OH!!!!! I think that worked. I think it gave me exactly what I wanted. You
are THE BOMB!!!!!!