VBA Button to find top candidates from table according to overall score

[Pantherlucky]

Hey, so I need to create a button in a separate worksheet that shows the top 8 scores from 20 entries in total (which are located in a column named "Total score" in Worskheet 1/my initial worsksheet) alongside the names of the top scorers. Out of the 8 top scores a message needs to pop up such that the top 2 are accepted to Department "A", the next 2 best scores (3,4) are accepted to Department "B" and the 5th best score is accepted to Department "C". For the last 3 of the top scorers (meaning 6,7,8) the message that must be shown is "Back ups".

 

[Ezguy4u]

Pantherlucky let's get the ball rolling. Now this might generate more questions then answers but we have to start some place. My 1st question is what if there is a tie? This program will just pick the scorer alphabetically. This should start the conversation.

Sub TopScor()


MsgBox "Top 2 go to Department A " & vbNewLine & Sheets("Worksheet 1").Cells(3, 4) & vbNewLine & Sheets("Worksheet 1").Cells(4, 4)
MsgBox "Top 3 & 4 go to Department B " & vbNewLine & Sheets("Worksheet 1").Cells(5, 4) & vbNewLine & Sheets("Worksheet 1").Cells(6, 4)
MsgBox "5th best score goes to Department C " & vbNewLine & Sheets("Worksheet 1").Cells(7, 4)
MsgBox "The 6, 7 & 8 are Back Ups" & vbNewLine & Sheets("Worksheet 1").Cells(8, 4) & vbNewLine & Sheets("Worksheet 1").Cells(9, 4) & vbNewLine & Sheets("Worksheet 1").Cells(10, 4)

End Sub

20-10-31 rank.xlsx
	B	C	D	E
1	Score
2	15		Top Scorers	Top 10 Total Score
3	9	1	A1	15
4	8	2	BG	12
5	7	3	BR	10
6	6	4	A2	9
7	5	5	A3	8
8	4	6	A6	7
9	3	7	A8	6
10	2	8	A9	5
11	1	9	AV	4
12	0	10	AZ	3
13	0
14	0
15	0
16	12
17	0
18	0
19	10
20	0
Worksheet 1

Cell Formulas
Range	Formula
D3:D12	D3	=IF(E3="","",INDEX($A$2:$A$20,AGGREGATE(15,6,(ROW($A$2:$A$20)-ROW($A$2)+1)/($B$2:$B$20=E3),COUNTIF(E$3:E3,E3))))
E3:E12	E3	=IFERROR(AGGREGATE(14,6,$B$2:$B$20/($B$2:$B$20>0),ROWS(E$3:E3)),"")

 

[Pantherlucky]

Greetings and thanks alot for the help! First of, there is no possibility that two candidates have the same grade (consider that as a given). So i solely need to create a button to scan my initial table or the specific column which includes the grades (let's say the column name is "Grades"), sort it in a descending order (perhaps?) and show the messages as stated above. As I said the specific button needs to be in a separate worksheet.

 

[ER_Neha]

Hello Panther , Please try this vba code , if it satisfy your requirement. IGNORE - the message box writing, you can manage that I suppose. However, the button for the macro must be created in the sheet where you have your entries, the new sheet (2) will contain the top 8 entries upon clicking the button.

Sub Macro()
Dim i As Double
Dim j As Double

Dim od As Worksheet
Dim nw As Worksheet

Set od = Sheets("Sheet1")
Set nw = Sheets("Sheet2")

For i = 1 To 8
    j = od.Application.WorksheetFunction.Match(od.Application.WorksheetFunction.Large(Range("b2:b20"), i), Range("b2:b20"), 0)
    nw.Range("a" & i & ":" & "b" & i).Value = od.Range("a" & j + 1 & ":" & "b" & j + 1).Value
Next i
MsgBox " Top 2 are accepted to Department A" & vbNewLine & _
"Next 2 (3,4)are accepted to Department B" & vbNewLine & _
"the 5th best score is accepted to Department C" & vbNewLine & _
"For the last 3 of the top scorers (meaning 6,7,8) Back ups"





End Sub

 

[Pantherlucky]

Hello Panther , Please try this vba code , if it satisfy your requirement. IGNORE - the message box writing, you can manage that I suppose. However, the button for the macro must be created in the sheet where you have your entries, the new sheet (2) will contain the top 8 entries upon clicking the button.

Sub Macro()
Dim i As Double
Dim j As Double

Dim od As Worksheet
Dim nw As Worksheet

Set od = Sheets("Sheet1")
Set nw = Sheets("Sheet2")

For i = 1 To 8
    j = od.Application.WorksheetFunction.Match(od.Application.WorksheetFunction.Large(Range("b2:b20"), i), Range("b2:b20"), 0)
    nw.Range("a" & i & ":" & "b" & i).Value = od.Range("a" & j + 1 & ":" & "b" & j + 1).Value
Next i
MsgBox " Top 2 are accepted to Department A" & vbNewLine & _
"Next 2 (3,4)are accepted to Department B" & vbNewLine & _
"the 5th best score is accepted to Department C" & vbNewLine & _
"For the last 3 of the top scorers (meaning 6,7,8) Back ups"





End Sub

[/C
[QUOTE="ER_Neha, post: 5580047, member: 464198"]
Hello Panther , Please try this vba code , if it satisfy your requirement. IGNORE - the message box writing, you can manage that I suppose. However, the button for the macro must be created in the sheet where you have your entries, the new sheet (2) will contain the top 8 entries upon clicking the button.[CODE=vba]
Sub Macro()
Dim i As Double
Dim j As Double

Dim od As Worksheet
Dim nw As Worksheet

Set od = Sheets("Sheet1")
Set nw = Sheets("Sheet2")

For i = 1 To 8
    j = od.Application.WorksheetFunction.Match(od.Application.WorksheetFunction.Large(Range("b2:b20"), i), Range("b2:b20"), 0)
    nw.Range("a" & i & ":" & "b" & i).Value = od.Range("a" & j + 1 & ":" & "b" & j + 1).Value
Next i
MsgBox " Top 2 are accepted to Department A" & vbNewLine & _
"Next 2 (3,4)are accepted to Department B" & vbNewLine & _
"the 5th best score is accepted to Department C" & vbNewLine & _
"For the last 3 of the top scorers (meaning 6,7,8) Back ups"





End Sub

Hello Panther , Please try this vba code , if it satisfy your requirement. IGNORE - the message box writing, you can manage that I suppose. However, the button for the macro must be created in the sheet where you have your entries, the new sheet (2) will contain the top 8 entries upon clicking the button.

Sub Macro()
Dim i As Double
Dim j As Double

Dim od As Worksheet
Dim nw As Worksheet

Set od = Sheets("Sheet1")
Set nw = Sheets("Sheet2")

For i = 1 To 8
    j = od.Application.WorksheetFunction.Match(od.Application.WorksheetFunction.Large(Range("b2:b20"), i), Range("b2:b20"), 0)
    nw.Range("a" & i & ":" & "b" & i).Value = od.Range("a" & j + 1 & ":" & "b" & j + 1).Value
Next i
MsgBox " Top 2 are accepted to Department A" & vbNewLine & _
"Next 2 (3,4)are accepted to Department B" & vbNewLine & _
"the 5th best score is accepted to Department C" & vbNewLine & _
"For the last 3 of the top scorers (meaning 6,7,8) Back ups"





End Sub

ODE]
[/QUOTE]
Could you guide me through this if you may? Am i suppose to put the "Grades" column on "b"? Also what does does "a" stand for? Finally as far as the message boxes are concerned, the name of the candidate needs to be shown aswell, not only the grades..so these two go together. Thus in another column there should be the corresponding name of the top 8 candidates because it needs to be shown in the message box ( imagine a list of the top 8 candidates with their respective grades along side with their acceptance in one of the above departements)

 

[Ezguy4u]

Pantherlucky ER_Neha's code is very good. But I am getting a feeling you are new to Excel and don't see the end result. So let me show you what it will look like.​

so this would be your initial Worksheet

Rank.xlsm
	A	B
1	Scorer	Grades
2	A1	16
3	A2	25
4	A3	20
5	A6	23
6	A8	15
7	A9	19
8	AV	10
9	AZ	24
10	B2	30
11	B3	29
12	B4	17
13	B5	18
14	BC	6
15	BE	4
16	BG	3
17	BI	2
18	BK	1
19	BR	11
20	BS	12
Sheet1

Now when you run that program your second sheet would look like this

Rank.xlsm
	A	B
1	B2	30
2	B3	29
3	A2	25
4	AZ	24
5	A6	23
6	A3	20
7	A9	19
8	B5	18
Sheet2

and you would get that pop up

Now in order to get that button you are talking about your second sheet would have to look like this

In order to get that button you need that Developer tab. Do you have or know how to install that tab? Also do you know how to add VBA code to your Excel Workbook?