## How to rank candidates by groups in Excel

In this 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 competition. After the entered data and timing results, the table is sorted ascending winning the place in order to ('G' column). So, to**rank the order of competitors**and sort ascending, use Excel RANK function and perform the sort.**Sheet 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.

Number next to one of the following markings above, is related to the number of years an individual competitor.

**M => Male**

F => FemaleF => Female

Number next to one of the following markings above, is related to the number of years an individual competitor.

**Sheet 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

=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 '

=IF((D2)<>"";(COUNTIF(D$2:D2;D2));"")

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));"")

**Sheet 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

=INDEX(Sheet1!$A$2:$A$21;MATCH(1;(

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

=INDEX(Sheet1!$A$2:$A$21;MATCH(1;(

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 '

=INDEX(Sheet1!$A$2:$A$21;MATCH(1;(

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 '

=INDEX(Sheet1!$A$2:$A$21;MATCH(1;(

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

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

**Sheet 2****Attention!**Pay attention to the separator in formulas. Maybe you need to use a

**comma (,)**instead of a

**semicolon (;)**.