Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting/Filtering
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting/Filtering
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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting/Filtering
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" wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting/Filtering
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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting/Filtering
OH!!!!! I think that worked. I think it gave me exactly what I wanted. You
are THE BOMB!!!!!! :) "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting/Filtering
Woohoo!!!
Brandy wrote: OH!!!!! I think that worked. I think it gave me exactly what I wanted. You are THE BOMB!!!!!! :) "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting and filtering results | Excel Worksheet Functions | |||
Excel - filtering, sorting? | Excel Worksheet Functions | |||
Sorting/Filtering data ??? | Excel Discussion (Misc queries) | |||
sorting and filtering | Excel Worksheet Functions | |||
Sorting and filtering | Excel Worksheet Functions |