| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching value of top 20 in a list!
hi all! i am having names in a1:a1000 and amounts in b1:b1000. how can i get the top20 highest amounts and its corresponding names in c1:d20? help pl? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508829 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching value of top 20 in a list!
Here's a non-array formulas play to extract a full descending sort by amt
into a new sheet (caters for the likely possibility of ties in the amounts) A sample construct is available at: http://cjoint.com/?cgiVSpaRSW ExtractDescendingSortedList_via135_wks.xls Assume source table in sheet: X, cols A & B, data from row2 down In a new sheet: Ranking, With the same col headers in A1:B1, viz.: Name, Amt Put in A2: =IF(ISERROR(LARGE($C:$C,ROW(A1))),"", INDEX(X!A:A,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0))) Copy A2 to B2 Put in C2: =IF(X!B2="","",X!B2-ROW()/10^10) (Leave C1 empty) [Col C is the arbitrary tiebreaker col] Select A2:C2, fill down to cover the max expected extent of the source data in X The above returns a full descending sort of the source table in X by the Amt col. Names with tied amts, if any, will appear in the same relative order that they appear in the list in X. Just pick off the "top 20" as required from the list (In the event of ties, or even multiple ties, you may need to pick more than just the top 20 names for the top 20 highest amts !) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "via135" wrote in message ... hi all! i am having names in a1:a1000 and amounts in b1:b1000. how can i get the top20 highest amounts and its corresponding names in c1:d20? help pl? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508829 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching value of top 20 in a list!
thks Mr Max! the formula works perfectly for creating a descending order list! but the problem is since i am having more than 20000 records it seems to be take a very long time for the application of the formulae! is there any other simpler method for picking the desired top valued items? also when the amount in the list equals "0" COL"C" throws some error, like the one, while entering more than 15 digits in a cell formarted general. -via135 Max Wrote: Here's a non-array formulas play to extract a full descending sort by amt into a new sheet (caters for the likely possibility of ties in the amounts) A sample construct is available at: http://cjoint.com/?cgiVSpaRSW ExtractDescendingSortedList_via135_wks.xls Assume source table in sheet: X, cols A & B, data from row2 down In a new sheet: Ranking, With the same col headers in A1:B1, viz.: Name, Amt Put in A2: =IF(ISERROR(LARGE($C:$C,ROW(A1))),"", INDEX(X!A:A,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0))) Copy A2 to B2 Put in C2: =IF(X!B2="","",X!B2-ROW()/10^10) (Leave C1 empty) [Col C is the arbitrary tiebreaker col] Select A2:C2, fill down to cover the max expected extent of the source data in X The above returns a full descending sort of the source table in X by the Amt col. Names with tied amts, if any, will appear in the same relative order that they appear in the list in X. Just pick off the "top 20" as required from the list (In the event of ties, or even multiple ties, you may need to pick more than just the top 20 names for the top 20 highest amts !) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "via135" wrote in message ... hi all! i am having names in a1:a1000 and amounts in b1:b1000. how can i get the top20 highest amounts and its corresponding names in c1:d20? help pl? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508829 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508829 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching value of top 20 in a list!
Select the table and sort by values, the first 20 names are those with
highest values Or apply a filter -- Regards, Peo Sjoblom Portland, Oregon "via135" wrote in message ... thks Mr Max! the formula works perfectly for creating a descending order list! but the problem is since i am having more than 20000 records it seems to be take a very long time for the application of the formulae! is there any other simpler method for picking the desired top valued items? also when the amount in the list equals "0" COL"C" throws some error, like the one, while entering more than 15 digits in a cell formarted general. -via135 Max Wrote: Here's a non-array formulas play to extract a full descending sort by amt into a new sheet (caters for the likely possibility of ties in the amounts) A sample construct is available at: http://cjoint.com/?cgiVSpaRSW ExtractDescendingSortedList_via135_wks.xls Assume source table in sheet: X, cols A & B, data from row2 down In a new sheet: Ranking, With the same col headers in A1:B1, viz.: Name, Amt Put in A2: =IF(ISERROR(LARGE($C:$C,ROW(A1))),"", INDEX(X!A:A,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0))) Copy A2 to B2 Put in C2: =IF(X!B2="","",X!B2-ROW()/10^10) (Leave C1 empty) [Col C is the arbitrary tiebreaker col] Select A2:C2, fill down to cover the max expected extent of the source data in X The above returns a full descending sort of the source table in X by the Amt col. Names with tied amts, if any, will appear in the same relative order that they appear in the list in X. Just pick off the "top 20" as required from the list (In the event of ties, or even multiple ties, you may need to pick more than just the top 20 names for the top 20 highest amts !) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "via135" wrote in message ... hi all! i am having names in a1:a1000 and amounts in b1:b1000. how can i get the top20 highest amounts and its corresponding names in c1:d20? help pl? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508829 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508829 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching value of top 20 in a list!
"via135" wrote:
... more than 20000 records it seems to be take a very long time for the application of the formulae! is there any other simpler method for picking the desired top valued items ? Try this: Copy only col C's formula all the way down to row20000 (this is required) Then copy A2:B2 down only as far as required to extract the desired top values, eg: to retrieve top 20, copy down to say B25, check the evaluated results, then copy down a few more lines if required (if there are ties). Set the calc mode to Manual. Do the formula fills, then press F9 to recalc. If you've got the results that you want, kill all formulas, then re-set calc mode back to Auto. .. when the amount in the list equals "0" COL"C" throws some error, like the one, while entering more than 15 digits in a cell formarted general. Col C is the arb tiebreaker. You can minimize or hide the col away. Ensure that only real numbers are entered under the "Amt" col -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching value of top 20 in a list!
thks Peo! i've asked for an alternate since i don't want to disturb the original sheet! -via135 Peo Sjoblom Wrote: Select the table and sort by values, the first 20 names are those with highest values Or apply a filter -- Regards, Peo Sjoblom Portland, Oregon "via135" wrote in message ... thks Mr Max! the formula works perfectly for creating a descending order list! but the problem is since i am having more than 20000 records it seems to be take a very long time for the application of the formulae! is there any other simpler method for picking the desired top valued items? also when the amount in the list equals "0" COL"C" throws some error, like the one, while entering more than 15 digits in a cell formarted general. -via135 Max Wrote: Here's a non-array formulas play to extract a full descending sort by amt into a new sheet (caters for the likely possibility of ties in the amounts) A sample construct is available at: http://cjoint.com/?cgiVSpaRSW ExtractDescendingSortedList_via135_wks.xls Assume source table in sheet: X, cols A & B, data from row2 down In a new sheet: Ranking, With the same col headers in A1:B1, viz.: Name, Amt Put in A2: =IF(ISERROR(LARGE($C:$C,ROW(A1))),"", INDEX(X!A:A,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0))) Copy A2 to B2 Put in C2: =IF(X!B2="","",X!B2-ROW()/10^10) (Leave C1 empty) [Col C is the arbitrary tiebreaker col] Select A2:C2, fill down to cover the max expected extent of the source data in X The above returns a full descending sort of the source table in X by the Amt col. Names with tied amts, if any, will appear in the same relative order that they appear in the list in X. Just pick off the "top 20" as required from the list (In the event of ties, or even multiple ties, you may need to pick more than just the top 20 names for the top 20 highest amts !) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "via135" wrote in message ... hi all! i am having names in a1:a1000 and amounts in b1:b1000. how can i get the top20 highest amounts and its corresponding names in c1:d20? help pl? -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508829 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508829 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508829 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching value of top 20 in a list!
thks Max! now it's quite ok! thks again for helping! -via135 Max Wrote: "via135" wrote: ... more than 20000 records it seems to be take a very long time for the application of the formulae! is there any other simpler method for picking the desired top valued items ? Try this: Copy only col C's formula all the way down to row20000 (this is required) Then copy A2:B2 down only as far as required to extract the desired top values, eg: to retrieve top 20, copy down to say B25, check the evaluated results, then copy down a few more lines if required (if there are ties). Set the calc mode to Manual. Do the formula fills, then press F9 to recalc. If you've got the results that you want, kill all formulas, then re-set calc mode back to Auto. .. when the amount in the list equals "0" COL"C" throws some error, like the one, while entering more than 15 digits in a cell formarted general. Col C is the arb tiebreaker. You can minimize or hide the col away. Ensure that only real numbers are entered under the "Amt" col -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=508829 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching value of top 20 in a list!
Pleasure` via135 !
Thanks for feeding back .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "via135" wrote in message ... thks Max! now it's quite ok! thks again for helping! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
| HOW DO I MAKE VALIDATION LIST CONTAING DATA FROM A DIFFERENT BO | Excel Discussion (Misc queries) | |||
| Extract multiple records matching criteria from list | Excel Worksheet Functions | |||
| Making pick list conditional on selection from previous pick list | Excel Discussion (Misc queries) | |||
| Matching a List Containing Redundant Values | Excel Discussion (Misc queries) | |||
Linear Mode
