Make list only passed students from all names

[kamaruddinjnv]

I have 40 students name.
I need pick name only passed students in a column rankwise without any gap of row.

 

[AhoyNC]

Looks like the formula in D4 in above post #10 may have gotten cut off:
It is an array formula and must be entered with CTRL-SHIFT-ENTER.
D4=

[TABLE="width: 1788"]
<colgroup><col width="1788"></colgroup><tbody>[TR]
   [TD="width: 1788"]IFERROR(INDEX($A$2:$A$8,MATCH(SMALL(IF($B$2:$B$8>$E$1,RANK($B$2:$B$8,$B$2:$B$8)+ROW($B$2:$B$8)/1000),ROWS($D$4:D4)),IF($B$2:$B$8>$E$1,RANK($B$2:$B$8,$B$2:$B$8)+ROW($B$2:$B$8)/1000),0)),"")[/TD]
 [/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Here is another option.
The formula in post #10 in cell D4 can be used without posting the grade.
The formula below requires that you have a descending column for the grades (cells E4:E9 in this example).

Both of these formulas are array formulas and both must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1	Name	Grade		Grades above	33
2	X	80
3	Y	70		Name	Grade
4	Z	60		N	90
5	P	80		X	80
6	H	20		P	80
7	J	45		Y	70
8	N	90		Z	60
9				J	45
10
Sheet

 

[kamaruddinjnv]

Wow.. Brilliant..
Perfect working.
Thanks

 

[Ingolf]

In D4 array formula (CTRL+SHIFT+ENTER)

{=IFERROR(INDEX($A$2:$A$8,LARGE(IF($B$2:$B$8>$E$1,ROW($B$2:$B$8)-ROW($B$1)),ROWS($D$4:D4))),"")}

	A	B	C	_____D	E
	Grades above

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: center"]Name
[/TD]
[TD="align: center"]Grade
[/TD]

[TD="align: center"]33
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]80
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: center"]Y
[/TD]
[TD="align: center"]70
[/TD]

[TD="align: center"]Name
[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: center"]Z
[/TD]
[TD="align: center"]60
[/TD]

[TD="align: center"]N
[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: center"]P
[/TD]
[TD="align: center"]80
[/TD]

[TD="align: center"]X
[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]20
[/TD]

[TD="align: center"]P
[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]45
[/TD]

[TD="align: center"]Y
[/TD]
[TD="align: center"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]90
[/TD]

[TD="align: center"]Z
[/TD]
[TD="align: center"][/TD]

[TD="align: center"]9
[/TD]

[TD="align: center"]J
[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10
[/TD]

</tbody>

 

[kamaruddinjnv]

Thanks for giving another solution.

 

[kamaruddinjnv]

If I change this formula in E4 <$E$1
For less than data then result came accurate but 0 shows for blank data. How I can blank these cell when data is blank.

 

[AhoyNC]

Just change my formula to:

[TABLE="width: 591"]
<colgroup><col width="591"></colgroup>[TR]
   [TD="width: 591"]IFERROR(LARGE(IF($B$2:$B$9<>"",IF($B$2:$B$9<$E$1,$B$2:$B$9)),ROWS($E$4:E4)),"")[/TD]
 [/TR]
[/TABLE]

You can also use this formula for greater than E1

Excel Workbook
	A	B	C	D	E
1	Name	Grade		Grades above	70
2	X	80
3	Y	70		Name	Grade
4	Z	60		Z	60
5	P			J	45
6	H	20		H	20
7	J	45
8	N	90
9	l
Sheet

 

[kamaruddinjnv]

Working perfectly.
Thanks a lot.

 

[kamaruddinjnv]

How can I find only top 10 and bottom 10 students list respectively column D, E and F,G

 

[kamaruddinjnv]

Hello sir,
I unable to post any new thread. I couldn't find new thread button. Am I blocked for this? Please help.