software

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: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=508829Here'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:
cjoint.com/?cgiVSpaRSW
ExtractDescendingSortedList_via135_wks.xls

Assume source table in sheet: X, cols A amp; 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))),quot;quot;,
INDEX(X!A:A,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0)))
Copy A2 to B2

Put in C2: =IF(X!B2=quot;quot;,quot;quot;,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 quot;top 20quot; 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
savefile.com/projects/236895
xdemechanik
---
quot;via135quot; gt; wrote in
message ...
gt;
gt; hi all!
gt;
gt; i am having names in a1:a1000 and amounts in b1:b1000.
gt; how can i get the top20 highest amounts and its corresponding names
gt; in c1:d20?
gt;
gt; help pl?
gt;
gt; -via135
gt;
gt;
gt; --
gt; via135
gt; ------------------------------------------------------------------------
gt; via135's Profile:
www.excelforum.com/member.php...oamp;userid=26725
gt; View this thread: www.excelforum.com/showthread...hreadid=508829
gt;

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 quot;0quot; COLquot;Cquot; throws some error,
like the one, while entering more than 15 digits in a cell formarted
general.

-via135
Max Wrote:
gt; Here's a non-array formulas play to extract a full descending sort by
gt; amt
gt; into a new sheet (caters for the likely possibility of ties in the
gt; amounts)
gt;
gt; A sample construct is available at:
gt; cjoint.com/?cgiVSpaRSW
gt; ExtractDescendingSortedList_via135_wks.xls
gt;
gt; Assume source table in sheet: X, cols A amp; B, data from row2 down
gt;
gt; In a new sheet: Ranking,
gt; With the same col headers in A1:B1, viz.: Name, Amt
gt;
gt; Put in A2:
gt; =IF(ISERROR(LARGE($C:$C,ROW(A1))),quot;quot;,
gt; INDEX(X!A:A,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0)))
gt; Copy A2 to B2
gt;
gt; Put in C2: =IF(X!B2=quot;quot;,quot;quot;,X!B2-ROW()/10^10)
gt; (Leave C1 empty)
gt;
gt; [Col C is the arbitrary tiebreaker col]
gt;
gt; Select A2:C2, fill down to cover
gt; the max expected extent of the source data in X
gt;
gt; The above returns a full descending sort of the source table in X by
gt; the Amt
gt; col. Names with tied amts, if any, will appear in the same relative
gt; order
gt; that they appear in the list in X. Just pick off the quot;top 20quot; as
gt; required
gt; from the list (In the event of ties, or even multiple ties, you may
gt; need to
gt; pick more than just the top 20 names for the top 20 highest amts !)
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;via135quot; gt; wrote
gt; in
gt; message ...
gt; gt;
gt; gt; hi all!
gt; gt;
gt; gt; i am having names in a1:a1000 and amounts in b1:b1000.
gt; gt; how can i get the top20 highest amounts and its corresponding names
gt; gt; in c1:d20?
gt; gt;
gt; gt; help pl?
gt; gt;
gt; gt; -via135
gt; gt;
gt; gt;
gt; gt; --
gt; gt; via135
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; via135's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26725
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=508829
gt; gt;--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=508829Select the table and sort by values, the first 20 names are those with
highest values
Or apply a filter

--
Regards,

Peo Sjoblom

Portland, Oregon

quot;via135quot; gt; wrote in
message ...
gt;
gt; thks Mr Max!
gt;
gt; the formula works perfectly for creating a descending order list!
gt; but the problem is since i am having more than 20000 records it seems
gt; to be take a very long time for the application of the formulae! is
gt; there any other simpler method for picking the desired top valued
gt; items?
gt;
gt; also when the amount in the list equals quot;0quot; COLquot;Cquot; throws some error,
gt; like the one, while entering more than 15 digits in a cell formarted
gt; general.
gt;
gt; -via135
gt;
gt;
gt;
gt;
gt;
gt;
gt; Max Wrote:
gt;gt; Here's a non-array formulas play to extract a full descending sort by
gt;gt; amt
gt;gt; into a new sheet (caters for the likely possibility of ties in the
gt;gt; amounts)
gt;gt;
gt;gt; A sample construct is available at:
gt;gt; cjoint.com/?cgiVSpaRSW
gt;gt; ExtractDescendingSortedList_via135_wks.xls
gt;gt;
gt;gt; Assume source table in sheet: X, cols A amp; B, data from row2 down
gt;gt;
gt;gt; In a new sheet: Ranking,
gt;gt; With the same col headers in A1:B1, viz.: Name, Amt
gt;gt;
gt;gt; Put in A2:
gt;gt; =IF(ISERROR(LARGE($C:$C,ROW(A1))),quot;quot;,
gt;gt; INDEX(X!A:A,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0)))
gt;gt; Copy A2 to B2
gt;gt;
gt;gt; Put in C2: =IF(X!B2=quot;quot;,quot;quot;,X!B2-ROW()/10^10)
gt;gt; (Leave C1 empty)
gt;gt;
gt;gt; [Col C is the arbitrary tiebreaker col]
gt;gt;
gt;gt; Select A2:C2, fill down to cover
gt;gt; the max expected extent of the source data in X
gt;gt;
gt;gt; The above returns a full descending sort of the source table in X by
gt;gt; the Amt
gt;gt; col. Names with tied amts, if any, will appear in the same relative
gt;gt; order
gt;gt; that they appear in the list in X. Just pick off the quot;top 20quot; as
gt;gt; required
gt;gt; from the list (In the event of ties, or even multiple ties, you may
gt;gt; need to
gt;gt; pick more than just the top 20 names for the top 20 highest amts !)
gt;gt; --
gt;gt; Max
gt;gt; Singapore
gt;gt; savefile.com/projects/236895
gt;gt; xdemechanik
gt;gt; ---
gt;gt; quot;via135quot; gt; wrote
gt;gt; in
gt;gt; message ...
gt;gt; gt;
gt;gt; gt; hi all!
gt;gt; gt;
gt;gt; gt; i am having names in a1:a1000 and amounts in b1:b1000.
gt;gt; gt; how can i get the top20 highest amounts and its corresponding names
gt;gt; gt; in c1:d20?
gt;gt; gt;
gt;gt; gt; help pl?
gt;gt; gt;
gt;gt; gt; -via135
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; via135
gt;gt; gt;
gt;gt; ------------------------------------------------------------------------
gt;gt; gt; via135's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=26725
gt;gt; gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=508829
gt;gt; gt;
gt;
gt;
gt; --
gt; via135
gt; ------------------------------------------------------------------------
gt; via135's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26725
gt; View this thread: www.excelforum.com/showthread...hreadid=508829
gt;quot;via135quot; wrote:
gt; ... more than 20000 records it seems
gt; to be take a very long time for the application of the formulae!
gt; is there any other simpler method for picking
gt; 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.

gt; .. when the amount in the list equals quot;0quot; COLquot;Cquot; throws some error,
gt; like the one, while entering more than 15 digits in a cell formarted
gt; general.

Col C is the arb tiebreaker. You can minimize or hide the col away.
Ensure that only real numbers are entered under the quot;Amtquot; col
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---

thks Peo!

i've asked for an alternate since i don't want to disturb the original
sheet!

-via135

Peo Sjoblom Wrote:
gt; Select the table and sort by values, the first 20 names are those with
gt; highest values
gt; Or apply a filter
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;via135quot; gt; wrote in
gt; message ...
gt; gt;
gt; gt; thks Mr Max!
gt; gt;
gt; gt; the formula works perfectly for creating a descending order list!
gt; gt; but the problem is since i am having more than 20000 records it
gt; seems
gt; gt; to be take a very long time for the application of the formulae! is
gt; gt; there any other simpler method for picking the desired top valued
gt; gt; items?
gt; gt;
gt; gt; also when the amount in the list equals quot;0quot; COLquot;Cquot; throws some
gt; error,
gt; gt; like the one, while entering more than 15 digits in a cell formarted
gt; gt; general.
gt; gt;
gt; gt; -via135
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; Max Wrote:
gt; gt;gt; Here's a non-array formulas play to extract a full descending sort
gt; by
gt; gt;gt; amt
gt; gt;gt; into a new sheet (caters for the likely possibility of ties in the
gt; gt;gt; amounts)
gt; gt;gt;
gt; gt;gt; A sample construct is available at:
gt; gt;gt; cjoint.com/?cgiVSpaRSW
gt; gt;gt; ExtractDescendingSortedList_via135_wks.xls
gt; gt;gt;
gt; gt;gt; Assume source table in sheet: X, cols A amp; B, data from row2 down
gt; gt;gt;
gt; gt;gt; In a new sheet: Ranking,
gt; gt;gt; With the same col headers in A1:B1, viz.: Name, Amt
gt; gt;gt;
gt; gt;gt; Put in A2:
gt; gt;gt; =IF(ISERROR(LARGE($C:$C,ROW(A1))),quot;quot;,
gt; gt;gt; INDEX(X!A:A,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0)))
gt; gt;gt; Copy A2 to B2
gt; gt;gt;
gt; gt;gt; Put in C2: =IF(X!B2=quot;quot;,quot;quot;,X!B2-ROW()/10^10)
gt; gt;gt; (Leave C1 empty)
gt; gt;gt;
gt; gt;gt; [Col C is the arbitrary tiebreaker col]
gt; gt;gt;
gt; gt;gt; Select A2:C2, fill down to cover
gt; gt;gt; the max expected extent of the source data in X
gt; gt;gt;
gt; gt;gt; The above returns a full descending sort of the source table in X
gt; by
gt; gt;gt; the Amt
gt; gt;gt; col. Names with tied amts, if any, will appear in the same
gt; relative
gt; gt;gt; order
gt; gt;gt; that they appear in the list in X. Just pick off the quot;top 20quot; as
gt; gt;gt; required
gt; gt;gt; from the list (In the event of ties, or even multiple ties, you may
gt; gt;gt; need to
gt; gt;gt; pick more than just the top 20 names for the top 20 highest amts !)
gt; gt;gt; --
gt; gt;gt; Max
gt; gt;gt; Singapore
gt; gt;gt; savefile.com/projects/236895
gt; gt;gt; xdemechanik
gt; gt;gt; ---
gt; gt;gt; quot;via135quot; gt;
gt; wrote
gt; gt;gt; in
gt; gt;gt; message ...
gt; gt;gt; gt;
gt; gt;gt; gt; hi all!
gt; gt;gt; gt;
gt; gt;gt; gt; i am having names in a1:a1000 and amounts in b1:b1000.
gt; gt;gt; gt; how can i get the top20 highest amounts and its corresponding
gt; names
gt; gt;gt; gt; in c1:d20?
gt; gt;gt; gt;
gt; gt;gt; gt; help pl?
gt; gt;gt; gt;
gt; gt;gt; gt; -via135
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt; --
gt; gt;gt; gt; via135
gt; gt;gt; gt;
gt; gt;gt;
gt; ------------------------------------------------------------------------
gt; gt;gt; gt; via135's Profile:
gt; gt;gt; www.excelforum.com/member.php...oamp;userid=26725
gt; gt;gt; gt; View this thread:
gt; gt;gt; www.excelforum.com/showthread...hreadid=508829
gt; gt;gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; via135
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; via135's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=26725
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=508829
gt; gt;--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=508829
thks Max!

now it's quite ok!
thks again for helping!

-via135

Max Wrote:
gt; quot;via135quot; wrote:
gt; gt; ... more than 20000 records it seems
gt; gt; to be take a very long time for the application of the formulae!
gt; gt; is there any other simpler method for picking
gt; gt; the desired top valued items ?
gt;
gt; Try this:
gt;
gt; Copy only col C's formula all the way down to row20000 (this is
gt; required)
gt; Then copy A2:B2 down only as far as required to extract the desired
gt; top
gt; values, eg: to retrieve top 20, copy down to say B25, check the
gt; evaluated
gt; results, then copy down a few more lines if required (if there are
gt; ties).
gt;
gt; Set the calc mode to Manual.
gt; Do the formula fills, then press F9 to recalc.
gt; If you've got the results that you want, kill all formulas,
gt; then re-set calc mode back to Auto.
gt;
gt; gt; .. when the amount in the list equals quot;0quot; COLquot;Cquot; throws some error,
gt; gt; like the one, while entering more than 15 digits in a cell formarted
gt; gt; general.
gt;
gt; Col C is the arb tiebreaker. You can minimize or hide the col away.
gt; Ensure that only real numbers are entered under the quot;Amtquot; col
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; -----
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=508829Pleasure` via135 !
Thanks for feeding back ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;via135quot; gt; wrote in
message ...
gt;
gt; thks Max!
gt;
gt; now it's quite ok!
gt; thks again for helping!