LinkBack URL
About LinkBacks
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
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
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" <via135.22smtm_1139205301.2705@excelforum-nospam.com> wrote in
message news:via135.22smtm_1139205301.2705@excelforum-nospam.com...
>
> 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
>
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
Originally Posted by Max
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" <via135.22tmxo_1139252102.26@excelforum-nospam.com> wrote in
message news:via135.22tmxo_1139252102.26@excelforum-nospam.com...
>
> 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" <via135.22smtm_1139205301.2705@excelforum-nospam.com> wrote
>> in
>> message news:via135.22smtm_1139205301.2705@excelforum-nospam.com...
>> >
>> > 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
>
thks Peo!
i've asked for an alternate since i don't want to disturb the original sheet!
-via135
"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
---
thks Max!
now it's quite ok!
thks again for helping!
-via135
Pleasure` via135 !
Thanks for feeding back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"via135" <via135.22vfzc_1139336409.5306@excelforum-nospam.com> wrote in
message news:via135.22vfzc_1139336409.5306@excelforum-nospam.com...
>
> thks Max!
>
> now it's quite ok!
> thks again for helping!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks