matching value of top 20 in a list!

[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

 

[Max]

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 !)

 

[via135]

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

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 !)

in
message news:[email protected]...

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?action=getinfo&userid=26725
View this thread: http://www.excelforum.com/showthread.php?threadid=508829

 

[Peo Sjoblom]

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

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

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 !)

in
message news:[email protected]...

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?action=getinfo&userid=26725
View this thread: http://www.excelforum.com/showthread.php?threadid=508829

 

[Max]

... 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

 

[via135]

thks Peo!

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

-via135

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

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

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 !)

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?action=getinfo&userid=26725
View this thread:
http://www.excelforum.com/showthread.php?threadid=508829

 

[via135]

thks Max!

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

-via135

 

[Max]

Pleasure` via135 !
Thanks for feeding back ..