Excel tutorials

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

How to rank the candidates by groups

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

How to display the results of competitors by most points scored or time

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

How to count the order of appearance of categories of competitors

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

 How to rank candidates by Category

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.