| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with custom sorting
Any help will be very appreciated!
Is there any way that I can custom sort a list in this way: I made up the list below. 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 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 with the largest amount is 1234 with amount 500, 350, 95 and 20. And so on... I want 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
|
|||
|
|||
|
Need help with custom sorting
Insert a new column next to your data (I'll use column C for this example).
Enter this formula into C1: =MAX(IF($A$1:$A$14=A1,$B$1:$B$14,0)) Enter this as an array formula (use CTRL-SHIFT-ENTER instead of just Enter). If done correctly, the formula should be surrounded by { }. Copy the formula down column C as far as needed. Then, when you sort, sort by Column C first, then by Column B. You can also hide this new column so that it is out of the way, and still use it for sorting. HTH, Elkar "etronis" wrote: Any help will be very appreciated! Is there any way that I can custom sort a list in this way: I made up the list below. 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 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 with the largest amount is 1234 with amount 500, 350, 95 and 20. And so on... I want 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help with custom sorting
see your first post.
etronis wrote: Any help will be very appreciated! Is there any way that I can custom sort a list in this way: I made up the list below. 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 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 with the largest amount is 1234 with amount 500, 350, 95 and 20. And so on... I want 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help with custom sorting
Dave Peterson wrote: see your first post. etronis wrote: Any help will be very appreciated! Is there any way that I can custom sort a list in this way: I made up the list below. 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 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 with the largest amount is 1234 with amount 500, 350, 95 and 20. And so on... I want 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 Thanks, But what I want is to tell excel to sort Item Numbers by the largest amount, so if there is one Item Number with several Amounts, and only one of these Amounts is the largest one in the list, then excel will sort this Item Number first even the rest of Amounts for this Item number might be smaller than other Amounts belonging to the other Item Numbers. Then excel will pick Item Number will second largest Amount and sort its amounts....Refer to the list above, which i sorted manually. I was wandering whether Excel has capabilities to build a custom list that will generate this kind of sorting. Thank you |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help with custom sorting
Elkar wrote: Insert a new column next to your data (I'll use column C for this example). Enter this formula into C1: =MAX(IF($A$1:$A$14=A1,$B$1:$B$14,0)) Enter this as an array formula (use CTRL-SHIFT-ENTER instead of just Enter). If done correctly, the formula should be surrounded by { }. Copy the formula down column C as far as needed. Then, when you sort, sort by Column C first, then by Column B. You can also hide this new column so that it is out of the way, and still use it for sorting. HTH, Elkar "etronis" wrote: Any help will be very appreciated! Is there any way that I can custom sort a list in this way: I made up the list below. 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 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 with the largest amount is 1234 with amount 500, 350, 95 and 20. And so on... I want 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, But what I want is to tell excel to sort Item Numbers by the largest amount, so if there is one Item Number with several Amounts, and only one of these Amounts is the largest one in the list, then excel will sort this Item Number first even the rest of Amounts for this Item number might be smaller than other Amounts belonging to the other Item Numbers. Then excel will pick Item Number will second largest Amount and sort its amounts....Refer to the list above, which i sorted manually. I was wandering whether Excel has capabilities to build a custom list that will generate this kind of sorting. Thank you |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help with custom sorting
One option would be a Pivot Table.
Drag Item, then Amount, to the Row area. Drag Amount to Data Area. (dc=Double Click) dc amount in Data area, and summarize by Max of Amount. dc Item in Row area, then Advanced, and sort Descending based on Max of Amount. dc Amount in Row area, then Advanced, and sort Descending also. Remove Subtotals to clean up. Hope this gets you started. -- HTH :) Dana DeLouis Windows XP & Office 2003 "etronis" wrote in message oups.com... Dave Peterson wrote: see your first post. etronis wrote: Any help will be very appreciated! Is there any way that I can custom sort a list in this way: I made up the list below. 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 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 with the largest amount is 1234 with amount 500, 350, 95 and 20. And so on... I want 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 Thanks, But what I want is to tell excel to sort Item Numbers by the largest amount, so if there is one Item Number with several Amounts, and only one of these Amounts is the largest one in the list, then excel will sort this Item Number first even the rest of Amounts for this Item number might be smaller than other Amounts belonging to the other Item Numbers. Then excel will pick Item Number will second largest Amount and sort its amounts....Refer to the list above, which i sorted manually. I was wandering whether Excel has capabilities to build a custom list that will generate this kind of sorting. Thank you |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help with custom sorting
I don't believe you can get Excel to recognize your sort pattern without the
use of a "helper" column. If you really need to avoid this additional column, then you're going to need to look into a VB/Macro solution. Perhaps someone else, maybe in the Excel/Programming group, can help you with that. "etronis" wrote: Elkar wrote: Insert a new column next to your data (I'll use column C for this example). Enter this formula into C1: =MAX(IF($A$1:$A$14=A1,$B$1:$B$14,0)) Enter this as an array formula (use CTRL-SHIFT-ENTER instead of just Enter). If done correctly, the formula should be surrounded by { }. Copy the formula down column C as far as needed. Then, when you sort, sort by Column C first, then by Column B. You can also hide this new column so that it is out of the way, and still use it for sorting. HTH, Elkar "etronis" wrote: Any help will be very appreciated! Is there any way that I can custom sort a list in this way: I made up the list below. 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 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 with the largest amount is 1234 with amount 500, 350, 95 and 20. And so on... I want 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, But what I want is to tell excel to sort Item Numbers by the largest amount, so if there is one Item Number with several Amounts, and only one of these Amounts is the largest one in the list, then excel will sort this Item Number first even the rest of Amounts for this Item number might be smaller than other Amounts belonging to the other Item Numbers. Then excel will pick Item Number will second largest Amount and sort its amounts....Refer to the list above, which i sorted manually. I was wandering whether Excel has capabilities to build a custom list that will generate this kind of sorting. Thank you |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help with custom sorting
Did you try it?
And I don't think that this is close to a custom list. etronis wrote: Dave Peterson wrote: see your first post. etronis wrote: Any help will be very appreciated! Is there any way that I can custom sort a list in this way: I made up the list below. 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 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 with the largest amount is 1234 with amount 500, 350, 95 and 20. And so on... I want 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 Thanks, But what I want is to tell excel to sort Item Numbers by the largest amount, so if there is one Item Number with several Amounts, and only one of these Amounts is the largest one in the list, then excel will sort this Item Number first even the rest of Amounts for this Item number might be smaller than other Amounts belonging to the other Item Numbers. Then excel will pick Item Number will second largest Amount and sort its amounts....Refer to the list above, which i sorted manually. I was wandering whether Excel has capabilities to build a custom list that will generate this kind of sorting. Thank you -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help with custom sorting
Dana DeLouis wrote:
One option would be a Pivot Table. Drag Item, then Amount, to the Row area. Drag Amount to Data Area. (dc=Double Click) dc amount in Data area, and summarize by Max of Amount. dc Item in Row area, then Advanced, and sort Descending based on Max of Amount. dc Amount in Row area, then Advanced, and sort Descending also. Remove Subtotals to clean up. Hope this gets you started. -- HTH :) Dana DeLouis Windows XP & Office 2003 "etronis" wrote in message oups.com... Dave Peterson wrote: see your first post. etronis wrote: Any help will be very appreciated! Is there any way that I can custom sort a list in this way: I made up the list below. 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 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 with the largest amount is 1234 with amount 500, 350, 95 and 20. And so on... I want 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 Thanks, But what I want is to tell excel to sort Item Numbers by the largest amount, so if there is one Item Number with several Amounts, and only one of these Amounts is the largest one in the list, then excel will sort this Item Number first even the rest of Amounts for this Item number might be smaller than other Amounts belonging to the other Item Numbers. Then excel will pick Item Number will second largest Amount and sort its amounts....Refer to the list above, which i sorted manually. I was wandering whether Excel has capabilities to build a custom list that will generate this kind of sorting. Thank you Can't do it with pivot table becouse those two columns are part of a larger database and it will mix up my whole spreadsheet. Doesn't excel have some kind of custom sorting, similiar to what we do with custom list from Tools, Options, Custom List tab? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom Sorting | Excel Discussion (Misc queries) | |||
| urgent solution needed to 'custom view' re-calculation problem! | Excel Discussion (Misc queries) | |||
| Custom Formats | Excel Discussion (Misc queries) | |||
| Custom charts - default formatting | Charts and Charting in Excel | |||
| Custom Views in Shared workbook | Excel Discussion (Misc queries) | |||
Linear Mode
