| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Sorting
Any help will be very appreciated:
Item Amount Number 1234 500 1234 20 1234 350 1234 95 5678 400 5678 200 5678 10 9101 600 9101 50 9101 5 9112 85 9112 155 9113 450 9115 256 I made up the above list. Is there any way that I can custom sort a list in this way whe first to have the Item number with the largest amount and then the same item number with their corresponding amount from high to low then have the second largest amount by item number and then the same item number with corresponding amount from high to low. for example: the item number with largest amount is 9101 with amount 600, 50 and 5 second item the largest amount is 1234 with 500, 350, 95 and 20 and so on, so the list will be sorted in this way: Item Amount Number 9101 600 9101 50 9101 5 1234 500 1234 350 1234 95 1234 20 9113 450 5678 400 5678 200 5678 10 9115 256 9112 155 9112 85 Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Custom Sorting
I put your test data in A1:B14.
In C1, I used this array formula: =MAX(IF($A$1:$A$14=A1,$B$1:$B$14)) In xl2007, you should be able to use the whole column. In earlier versions, you can't--so adjust the range accordingly. 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.) Then drag this array formula down the column. The select A1:Cxxx and Data|Sort Sort by column C in Descending order sort by column A in Ascending order sort by column B in Descending order. Then delete that stuff in column C if you don't need it anymore. When I did it, I got this (including column C): 9101 600 600 9101 50 600 9101 5 600 1234 500 500 1234 350 500 1234 95 500 1234 20 500 9113 450 450 5678 400 400 5678 200 400 5678 10 400 9115 256 256 9112 155 155 9112 85 155 etronis wrote: Any help will be very appreciated: Item Amount Number 1234 500 1234 20 1234 350 1234 95 5678 400 5678 200 5678 10 9101 600 9101 50 9101 5 9112 85 9112 155 9113 450 9115 256 I made up the above list. Is there any way that I can custom sort a list in this way whe first to have the Item number with the largest amount and then the same item number with their corresponding amount from high to low then have the second largest amount by item number and then the same item number with corresponding amount from high to low. for example: the item number with largest amount is 9101 with amount 600, 50 and 5 second item the largest amount is 1234 with 500, 350, 95 and 20 and so on, so the list will be sorted in this way: Item Amount Number 9101 600 9101 50 9101 5 1234 500 1234 350 1234 95 1234 20 9113 450 5678 400 5678 200 5678 10 9115 256 9112 155 9112 85 Thanks -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Custom Sorting
Try something like this:
First, here are the steps 1)Sort the list by Item (asc) and Amount (desc) 2)Apply Subtotals...but, using MAX instead of sum 3)Collapse the Subtotals and sort by Amount (desc) 4)Expand Subtotals 5)Remove all Subtotals With your posted data in A1:B15 (A1 contains "Item Number" Then Select A1:B15 From the Excel main menu: <data<subtotals At each change in: Item Use function: MAX Add Subtotals to: Amount Click [OK] Click Outline button [2] <data<sort Sort by: Amount (desc) Then by: Item Click the [OK] button Click Outline button [3] <data<subtotals Click [remove all subtotals] Is that something you can work with? *********** Regards, Ron XL2002, WinXP "etronis" wrote: Any help will be very appreciated: Item Amount Number 1234 500 1234 20 1234 350 1234 95 5678 400 5678 200 5678 10 9101 600 9101 50 9101 5 9112 85 9112 155 9113 450 9115 256 I made up the above list. Is there any way that I can custom sort a list in this way whe first to have the Item number with the largest amount and then the same item number with their corresponding amount from high to low then have the second largest amount by item number and then the same item number with corresponding amount from high to low. for example: the item number with largest amount is 9101 with amount 600, 50 and 5 second item the largest amount is 1234 with 500, 350, 95 and 20 and so on, so the list will be sorted in this way: Item Amount Number 9101 600 9101 50 9101 5 1234 500 1234 350 1234 95 1234 20 9113 450 5678 400 5678 200 5678 10 9115 256 9112 155 9112 85 Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Custom Sorting
Ron Coderre wrote: Try something like this: First, here are the steps 1)Sort the list by Item (asc) and Amount (desc) 2)Apply Subtotals...but, using MAX instead of sum 3)Collapse the Subtotals and sort by Amount (desc) 4)Expand Subtotals 5)Remove all Subtotals With your posted data in A1:B15 (A1 contains "Item Number" Then Select A1:B15 From the Excel main menu: <data<subtotals At each change in: Item Use function: MAX Add Subtotals to: Amount Click [OK] Click Outline button [2] <data<sort Sort by: Amount (desc) Then by: Item Click the [OK] button Click Outline button [3] <data<subtotals Click [remove all subtotals] Is that something you can work with? *********** Regards, Ron XL2002, WinXP "etronis" wrote: Any help will be very appreciated: Item Amount Number 1234 500 1234 20 1234 350 1234 95 5678 400 5678 200 5678 10 9101 600 9101 50 9101 5 9112 85 9112 155 9113 450 9115 256 I made up the above list. Is there any way that I can custom sort a list in this way whe first to have the Item number with the largest amount and then the same item number with their corresponding amount from high to low then have the second largest amount by item number and then the same item number with corresponding amount from high to low. for example: the item number with largest amount is 9101 with amount 600, 50 and 5 second item the largest amount is 1234 with 500, 350, 95 and 20 and so on, so the list will be sorted in this way: Item Amount Number 9101 600 9101 50 9101 5 1234 500 1234 350 1234 95 1234 20 9113 450 5678 400 5678 200 5678 10 9115 256 9112 155 9112 85 Thanks Thanks Ron, Can you check my other mesage :Need help with Custom Sorting". I am trying to do something simpler, kind of custom list thing. I am not sure whether this is feasable in excel for that patern of sorting I want. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Custom Sorting
When i used your posted data and performed the 5 Steps described in my post,
my end result was this list: Item Amount 9101 600 9101 50 9101 5 1234 500 1234 350 1234 95 1234 20 9113 450 5678 400 5678 200 5678 10 9115 256 9112 155 9112 85 Isn't that exactly what you said you needed? Or...am I missing something? *********** Regards, Ron XL2002, WinXP "etronis" wrote: Ron Coderre wrote: Try something like this: First, here are the steps 1)Sort the list by Item (asc) and Amount (desc) 2)Apply Subtotals...but, using MAX instead of sum 3)Collapse the Subtotals and sort by Amount (desc) 4)Expand Subtotals 5)Remove all Subtotals With your posted data in A1:B15 (A1 contains "Item Number" Then Select A1:B15 From the Excel main menu: <data<subtotals At each change in: Item Use function: MAX Add Subtotals to: Amount Click [OK] Click Outline button [2] <data<sort Sort by: Amount (desc) Then by: Item Click the [OK] button Click Outline button [3] <data<subtotals Click [remove all subtotals] Is that something you can work with? *********** Regards, Ron XL2002, WinXP "etronis" wrote: Any help will be very appreciated: Item Amount Number 1234 500 1234 20 1234 350 1234 95 5678 400 5678 200 5678 10 9101 600 9101 50 9101 5 9112 85 9112 155 9113 450 9115 256 I made up the above list. Is there any way that I can custom sort a list in this way whe first to have the Item number with the largest amount and then the same item number with their corresponding amount from high to low then have the second largest amount by item number and then the same item number with corresponding amount from high to low. for example: the item number with largest amount is 9101 with amount 600, 50 and 5 second item the largest amount is 1234 with 500, 350, 95 and 20 and so on, so the list will be sorted in this way: Item Amount Number 9101 600 9101 50 9101 5 1234 500 1234 350 1234 95 1234 20 9113 450 5678 400 5678 200 5678 10 9115 256 9112 155 9112 85 Thanks Thanks Ron, Can you check my other mesage :Need help with Custom Sorting". I am trying to do something simpler, kind of custom list thing. I am not sure whether this is feasable in excel for that patern of sorting I want. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Custom Sorting
Ron Coderre wrote:
When i used your posted data and performed the 5 Steps described in my post, my end result was this list: Item Amount 9101 600 9101 50 9101 5 1234 500 1234 350 1234 95 1234 20 9113 450 5678 400 5678 200 5678 10 9115 256 9112 155 9112 85 Isn't that exactly what you said you needed? Or...am I missing something? *********** Regards, Ron XL2002, WinXP "etronis" wrote: Ron Coderre wrote: Try something like this: First, here are the steps 1)Sort the list by Item (asc) and Amount (desc) 2)Apply Subtotals...but, using MAX instead of sum 3)Collapse the Subtotals and sort by Amount (desc) 4)Expand Subtotals 5)Remove all Subtotals With your posted data in A1:B15 (A1 contains "Item Number" Then Select A1:B15 From the Excel main menu: <data<subtotals At each change in: Item Use function: MAX Add Subtotals to: Amount Click [OK] Click Outline button [2] <data<sort Sort by: Amount (desc) Then by: Item Click the [OK] button Click Outline button [3] <data<subtotals Click [remove all subtotals] Is that something you can work with? *********** Regards, Ron XL2002, WinXP "etronis" wrote: Any help will be very appreciated: Item Amount Number 1234 500 1234 20 1234 350 1234 95 5678 400 5678 200 5678 10 9101 600 9101 50 9101 5 9112 85 9112 155 9113 450 9115 256 I made up the above list. Is there any way that I can custom sort a list in this way whe first to have the Item number with the largest amount and then the same item number with their corresponding amount from high to low then have the second largest amount by item number and then the same item number with corresponding amount from high to low. for example: the item number with largest amount is 9101 with amount 600, 50 and 5 second item the largest amount is 1234 with 500, 350, 95 and 20 and so on, so the list will be sorted in this way: Item Amount Number 9101 600 9101 50 9101 5 1234 500 1234 350 1234 95 1234 20 9113 450 5678 400 5678 200 5678 10 9115 256 9112 155 9112 85 Thanks Thanks Ron, Can you check my other mesage :Need help with Custom Sorting". I am trying to do something simpler, kind of custom list thing. I am not sure whether this is feasable in excel for that patern of sorting I want. Yes, the end result is the same, but I was wondering whether is there anything simplier in excel rather than going through those steps. Doesn't excel have some kind of custom sorting, similiar to what we do with custom list from Tools, Options, Custom List tab? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| urgent solution needed to 'custom view' re-calculation problem! | Excel Discussion (Misc queries) | |||
| Custom Formats | Excel Discussion (Misc queries) | |||
| Using/referencing custom lists | Excel Worksheet Functions | |||
| Custom charts - default formatting | Charts and Charting in Excel | |||
| Custom Views in Shared workbook | Excel Discussion (Misc queries) | |||
Linear Mode
