Large Function returning top values

[J Shrimps, Jr.]

Have a list of several hundred records.
Each record has a unique name in column A
and a value in column B.
Would like to know the top/bottom values
associated with each record, WITHOUT
SORTING THE COLUMN(S).

Someone on TechRepublic posted this
possible solution, which would sums
the five largest values in Column B.

Index(Large(B1:B100,{1,2,3,4,5}),1,1)
Index(Large(Range,Rank(s)),Row,Column)

Now I need to pull the values (names)
in Column A that are on the same row
as the five largest values in Column B.

I don't understand the second line -
Index(Large(Range,Rank(s)),Row,Column) -
or if that is the right way to go about this.
I'm looking for a column of cells with
the top five values in one column and
the name from column A next to it.
Result would look like this:
Rank Col. C Col. D
1 Smith 100,000
2 Johnson 99,000
3 Jones 85,000
4 Roberts 79,000
5 Davis 66,000
Maybe some unholy concatenation of
Vlookup. I don't care how complicated
it is.

 

[Max]

One play which auto-extracts a full descending list
(inclusive of ties), using non-array formulas

Assume source data in cols A & B, from row1 down

In C1:
=IF(ISERROR(LARGE($E:$E,ROW())),"",
INDEX(A:A,MATCH(LARGE($E:$E,ROW()),$E:$E,0)))
Copy C1 to D1

In E1: =IF(B1="","",B1-ROW()/10^10)
(this is an arb tie-breaker for descending sort)

Select C1:E1, fill down to say, E100,
to cover the max expected extent of data in cols A & B

Cols C & D will return a full descending sort of the names and corresponding
values within cols A & B, sorted by the values in col B. Names with tied
values (if any) will appear in the same relative order that they are in cols
A & B. Just read off the top x names & values as desired in cols C & D.

 

[Max]

Here's a sample construct to illustrate:
http://cjoint.com/?dCdKkRUBfI
Auto-Extracting Full Descending Sort.xls

 

[Aladin Akyurek]

Let A3:B1 house the following sample:

Name,Score
dawn,23
damon,25
dan,25
chris,22
christine,25
ian,32
john,35
brian,35

C4, copied down:

=RANK(B4,$B$4:$B$11)+COUNTIF(B4:$B$4,B4)-1

D1: 5

which means: We want a Top 5 list of the highest-valued performers.

D2:

=MAX(IF(INDEX(B4:B11,MATCH(D1,C4:C11,0))=B4:B11,C4:C11))-D1

which must be confirmed with control+shift+enter, not just with enter.

This determines the number of ties the 5th performer might have.

D4, copied down:

=IF(ROWS(D$44)<=$D$1+$D$2,MATCH(ROWS(D$44),$C$4:$C$11,0),"")

E4, copied across to F5 then down:

=IF(N($D4),INDEX(A$4:A$11,$D4),"")

 

[J Shrimps, Jr.]

Most Excellent.
However, I changed the formula slightly, adding
=IF(ISERROR(LARGE($E1:E100,ROW())),"",
INDEX(A1:A100,MATCH(LARGE(E1:E100,ROW()),E1:E100,0)))
so the range is from row 1 to 100.
However, if I insert a row at the top of the spreadsheet,
the formula then returns not the largest value, but the
second largest value as value #1, and so on.
The last value in the column is blank.
I need to put headers in the top of the
columns. How can I change the formula to make
that possible. Formula only seems to work if
the data starts on row 1.

P.S. I have a brother in Singapore.
You probably know him.
Singapore's not very big, is it?

 

[Max]

I need to put headers in the top of the columns.

How can I change the formula to make that possible.

If we insert a top header row (data now from row2 down),
we could just revise the earlier formulas to:

Put in C2:
=IF(ISERROR(LARGE($E:$E,ROW()-1)),"",
INDEX(A:A,MATCH(LARGE($E:$E,ROW()-1),$E:$E,0)))
Copy C2 to D2

[ ROW()-1 replaces ROW() ]

Put in E2: =IF(B2="","",B2-ROW()/10^10)
(leave E1 empty)

Select C2:E2, fill down
Cols C & D will return the correct results

P.S. I have a brother in Singapore.
You probably know him.
Singapore's not very big, is it?

Singapore's a country with around 4 million plus residents
(.. so I may not know your brother <g>)

 

[J Shrimps, Jr.]

Kidding about the Singapore, although my brother really does live there.

On a serious note, what is the purpose of the (10^10)?
And why not have the formula just =B2?

I need to put headers in the top of the columns.
How can I change the formula to make that possible.

If we insert a top header row (data now from row2 down),
we could just revise the earlier formulas to:

Put in C2:
=IF(ISERROR(LARGE($E:$E,ROW()-1)),"",
INDEX(A:A,MATCH(LARGE($E:$E,ROW()-1),$E:$E,0)))
Copy C2 to D2

[ ROW()-1 replaces ROW() ]

Put in E2: =IF(B2="","",B2-ROW()/10^10)
(leave E1 empty)

Select C2:E2, fill down
Cols C & D will return the correct results

P.S. I have a brother in Singapore.
You probably know him.
Singapore's not very big, is it?

Singapore's a country with around 4 million plus residents
(.. so I may not know your brother <g>)

Most Excellent.
However, I changed the formula slightly, adding
=IF(ISERROR(LARGE($E1:E100,ROW())),"",
INDEX(A1:A100,MATCH(LARGE(E1:E100,ROW()),E1:E100,0)))
so the range is from row 1 to 100.
However, if I insert a row at the top of the spreadsheet,
the formula then returns not the largest value, but the
second largest value as value #1, and so on.
The last value in the column is blank.
I need to put headers in the top of the
columns. How can I change the formula to make
that possible. Formula only seems to work if
the data starts on row 1.

P.S. I have a brother in Singapore.
You probably know him.
Singapore's not very big, is it?

 

[Max]

.. On a serious note, what is the purpose of the (10^10)?
And why not have the formula just =B2?

It's part of the expression in the arbitrary tiebreaker col E,
required to cater for the possibility of ties within the values in col B

If we don't use it, then we may not be able to pull out lines
which have identical values within col B

In col E, the part " ROW()/10^10 " when copied down
incrementally yields a fine enough distinction
between successive lines (for tied values within col B, if any)
w/o disrupting the scale of the values within col B

 

[Max]

And why not have the formula just =B2?

You can, if you're certain that there's going to be
no possibility of ties occuring in the values within col B

The earlier suggestion caters for the possibility of ties,
which covers the bigger ambit