How to Rank And Group Of Competitors By Category And Their Order
How to Rank Candidates by Groups in Excel
In this Excel example tutorial I will show how we can, as a result, to return the names of candidates and rank them by groups or categories, which are listed of competition. The picture below on the worksheet "Sheet1 'you notice, the situation after the completion of of competition. After the entered data and timing results, the table is sorted ascending winning place in order to ('G' column). So, to rank the order of competitors and sort ascending, use Excel RANK function and perform the sort.
Worksheet 1
Our goal is using Excel formulas to fill in the table on the worksheet 'Sheet2'. You notice that in this competition involved more in the running category, which are classified into groups.
- M => Male
- F => Female
Number next to one of the following markings above, is related to the number of years an individual competitor.
Worksheet 2
Solving this problem I made the following way.
On the worksheet "Sheet1" I added three extra columns, in which I have prepared a starting point for further calculations on the worksheet 'Sheet2'.
In the cell 'I2' is the following formula. (This formula separates the first character from the 'D' columns, in which there are categories of competitors. The formula is copied to the last row).
=LEFT(D2;1)
In the cell 'J2' is the following formula. (This formula is linked to 'I' column, it performs the counting order of occurrence data from the 'I' columns. The formula is the basis for further calculations on the worksheet 'Sheet2' in the 'B' column in which the ranking of candidates belonging to the 'M' or 'F' category competitors.)
=IF((I2)<>"";(COUNTIF(I$2:I2;I2));"")
In the cell 'K2' is the following formula. (This formula is linked to 'D' column, it performs counting occurrence of duplicate data. The formula is the basis for further calculations on the worksheet 'Sheet2' in the 'B' column in which the ranking of candidates belonging to the 'M??' or 'F??' category competitors.)
=IF((D2)<>"";(COUNTIF(D$2:D2;D2));"")
Worksheet 1
Ranking of Competitors by Category
The decision ranking contestants categories was made on the worksheet 'Sheet2'. You notice it in the picture below. In the 'A' column are groups or categories for which we want to rank the candidates. When I was thinking about how to solve the ranking of competitors by groups, I wondered which is the most important condition, which would enable further calculations. One might think that the important achieved time to order or position of winning a race. I decided that my main requirement is the starting number of competitors. For each group created a special formula (which are similar).
ARRAY formula in cell "B2" is as follows. (this formula is linked to 'A' column and the information in the worksheet "Sheet1". The formula is copied to 'B4' cell). The CSE (Ctrl+Shift+Enter) formula returns a start number of competitors who scored first place in the category of 'M'.
=INDEX(Sheet1!$A$2:$A$21;MATCH(1;(MID(A2;5;1)=Sheet1!$D$2:$D$21)*(ROW(Sheet1!A1)=Sheet1!$J$2:$J$21);0))
ARRAY formula in cell 'B6' is as follows. (this formula is linked to 'A' column and the data in the worksheet "Sheet1". The formula is copied to the 'B9' cell). The CSE (Ctrl+Shift+Enter) formula returns a start number of competitors who scored first place in the category 'M??'.
=INDEX(Sheet1!$A$2:$A$21;MATCH(1;(RIGHT(A6;3)=Sheet1!$D$2:$D$21)*(ROW(Sheet1!$A$1)=Sheet1!$K$2:$K$21);0))
ARRAY formula in cell "B11" is as follows. (this formula is linked to 'A' column and the data in the worksheet "Sheet1". The formula is copied to the 'B13' cell). The CSE (Ctrl+Shift+Enter) formula returns a start number of competitors who scored first place in the category 'F'.
=INDEX(Sheet1!$A$2:$A$21;MATCH(1;(MID(A11;5;1)=Sheet1!$I$2:$I$21)*(ROW(Sheet1!A2)=Sheet1!$J$2:$J$21);0))
ARRAY formula in cell "B15" is as follows. (this formula is linked to 'A' column and the data in the worksheet "Sheet1". The formula is copied to the 'B17' cells). The CSE (Ctrl+Shift+Enter) formula returns a start number of competitors who scored first place in the category 'F??'.
=INDEX(Sheet1!$A$2:$A$21;MATCH(1;(RIGHT(A15;3)=Sheet1!$D$2:$D$21)*(ROW(Sheet1!$A$1)=Sheet1!$K$2:$K$21);0))
Furthermore, the formula for other columns in which the competitors are ranked by the following groups
The formula in cell 'C2' is as follows (this formula is copied to the right until the last column and then down to the last row)
=VLOOKUP($B2;Sheet1!$A$2:$G$21;COLUMN(Sheet1!B$1);FALSE)
Analysis of competition and ranking of competitors on the basis of points or places.
The Final Appearance of Ranking Candidates by Category
Worksheet 2
Pay attention, you may need as a separator of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.