Make list only passed students from all names

kamaruddinjnv

Board Regular
Joined
Mar 14, 2014
Messages
51
I have 40 students name.
I need pick name only passed students in a column rankwise without any gap of row.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

kamaruddinjnv

Board Regular
Joined
Mar 14, 2014
Messages
51
Example
A B
1. X. 80
2. Y. 70
3. Z. 60
4. P. 32
5. H. 20
6. J. 45
7. N. 90

Make a list in c column only greater than 33% students rankwise without any row gap.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,101
Start by putting headers in row 1 of your sheet so that you data starts in row 2 and then run this macro:
Code:
Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A1:B" & LastRow).AutoFilter Field:=2, Criteria1:=">33"
    Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Copy Cells(2, 3)
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here is a formula way if you are interested.
I wasn't sure when you said grades above 33% if you are doing a ranking on the grades or just grades above 33 in your column B.

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Then drag formula down column as needed.
Excel Workbook
ABCDE
1NameGradeGrades above33
2X80
3Y70Name
4Z60X
5P32Y
6H20Z
7J45J
8N90N
Sheet
 

kamaruddinjnv

Board Regular
Joined
Mar 14, 2014
Messages
51

ADVERTISEMENT

Thanks a lot. It is nicely working. It's great help.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback.
 

kamaruddinjnv

Board Regular
Joined
Mar 14, 2014
Messages
51
Thanks. It's working nicely but I want name list according to decending order of marks automatically. Is it possible?
 
Last edited:

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
This should do what you want and will handle duplicate grades.

Formula in D4 below is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formulas down as needed.
Excel Workbook
ABCDE
1NameGradeGrades above33
2X80
3Y70NameGrade
4Z60N90
5P80X80
6H20P80
7J45Y70
8N90Z60
9J45
Sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,122,380
Messages
5,595,832
Members
414,024
Latest member
Woodsa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top