Returning the Highest values in a column

[pablorynx]

Imagine two columns of cells, one containing text, the other containing values.
In the first cells of the third column, I want to display the four largest values from the second column.
In the first cells of the fourth column, I want to display the names associated with those values.

 

[phantom1975]

WELCOME TO THE BOARD

I hope this is what you are looking for:

Book1
	A	B	C	D
1	APPLE	7	75	CARROT
2	BANANA	23	71	FIGS
3	CARROT	75	23	BANANA
4	DATE	2	8	EGGPLANT
5	EGGPLANT	8
6	FIGS	71
Sheet3

 

[Aladin Akyurek]

Imagine two columns of cells, one containing text, the other containing values.
In the first cells of the third column, I want to display the four largest values from the second column.
In the first cells of the fourth column, I want to display the names associated with those values.

Top4.xls
	A	B	C	D	E
1				1	4
2	Name	Score	Rank	Top4	Y
3	dawn	23	4	john	35
4	damon	23	5	ian	32
5	bob	25	3	bob	25
6	chris	22	6	dawn	23
7	christine	21	7	damon	23
8	ian	32	2
9	john	35	1
Data

Formulas...

D1:

=MAX(IF(INDEX(B3:B9,MATCH(E1,C3:C9,0))=B3:B9,C3:C9))-E1

which must be confirmed with control+shift+enter instead of just with enter.

D3:

=IF(E3<>"",INDEX($A$3:$A$9,MATCH(ROW()-ROW($D$3)+1,$C$3:$C$9,0)),"")

E3:

=IF(ROW()-ROW($E$3)+1<=$E$1+$D$1,INDEX($B$3:$B$9,MATCH(ROW()-ROW($E$3)+1,$C$3:$C$9,0)),"")

 

[Aladin Akyurek]

...

Formulas...

D1:

=MAX(IF(INDEX(B3:B9,MATCH(E1,C3:C9,0))=B3:B9,C3:C9))-E1

which must be confirmed with control+shift+enter instead of just with enter.

D3:

=IF(E3<>"",INDEX($A$3:$A$9,MATCH(ROW()-ROW($D$3)+1,$C$3:$C$9,0)),"")

E3:

=IF(ROW()-ROW($E$3)+1<=$E$1+$D$1,INDEX($B$3:$B$9,MATCH(ROW()-ROW($E$3)+1,$C$3:$C$9,0)),"")

The formula in C3, in case the exhibit gets unreadable, is:

=RANK(B3,$B$3:$B$9)+COUNTIF(B3:$B$3,B3)-1

 

[PrinceNeptune]

Okay, trying to follow all of this

=MAX(IF(INDEX(B3:B9,MATCH(E1,C3:C9,0))=B3:B9,C3:C9))-E1

Where B is my list of numbers (I need the top 20). But what is E1 and the C Range?

 

[Aladin Akyurek]

Okay, trying to follow all of this

=MAX(IF(INDEX(B3:B9,MATCH(E1,C3:C9,0))=B3:B9,C3:C9))-E1

Where B is my list of numbers (I need the top 20). But what is E1 and the C Range?

In your case, you enter 20 in E1.

C-range (C3:C9) is the range where scores/values from B3:B9 are ranked.

 

[PrinceNeptune]

Then for this to work do I need to rank my data first?

 

[Aladin Akyurek]

Then for this to work do I need to rank my data first?

Yes, of course.

 

[PrinceNeptune]

Okay, so I am breaking this all down and appreciate the help.

However, when I try to rank my data, I am still getting duplicates.

If you take the Rank command and just rank the two cells (using your above example) for 23 ... both will return the same rank. One rank 23 is not higher or lower than the other rank 23, therefore the countif command is actually useless (since it is counting both 23's and subtracting 1, therefore it is a constant).

The problem is getting it to rank the first 23 as the 4th and the second 23 as the 5th. Am I getting a different result than yours or missing a step here somewhere? I believe that the rank command is the first step of the entire sheet.

 

[Aladin Akyurek]

Okay, so I am breaking this all down and appreciate the help.

However, when I try to rank my data, I am still getting duplicates.

If you take the Rank command and just rank the two cells (using your above example) for 23 ... both will return the same rank. One rank 23 is not higher or lower than the other rank 23, therefore the countif command is actually useless (since it is counting both 23's and subtracting 1, therefore it is a constant).

The problem is getting it to rank the first 23 as the 4th and the second 23 as the 5th. Am I getting a different result than yours or missing a step here somewhere? I believe that the rank command is the first step of the entire sheet.

1. Try either to rebuild the case discussed in this thread until you obtain the same results as the exhibit shows in order to secure an understanding of the steps involved...

2. Or to provide a small sample from your data.