I need to arrange cells in excel leaderboard in a way that would show on what place each team was on each stage [closed]

Picture shows a desired outcome typed in manually. Cells need to be automatically arranged in a way that would show on what place each team was on each stage of a competition with their points, meaning last row would show final outcome of a competition. But also it would be clear who was leading on the second stage, who on third and so on

Solution 1:

This problem is easier to address if the colour coding is removed. Essentially, the problem is one of identifying team placings (1st, 2nd, 3rd, etc) after a number of rounds in which each team gains points where the placings are based on each team's cumulative points immediately after the round concerned.

In the screenshot below I have removed the colour coding and tried to avoid confusing team names with points and places by renaming the 5 teams using the letters A-E.

Table 1 is just the input of points scored by each team in each round. Table 2 is simply these points cumulated by round for each team. Table 3, which uses the SORTBY function, simply ranks the teams in order based on the points in Table 2. Table 3 could be the end of the matter but for the fact that teams might have scored an equal number points.

In fact, with the input data used this situation occurs after every round. For example, after Round 1 teams B and D both have 5 points. So, rather than showing Team B in place 1 and Team D in place 2, Table 3 should ideally show Teams B and D occupying joint 1st place.

The SORTBY function used to construct Table 3 deals with equalities in its sort criteria (cumulative points scored) by listing equally ranked items in the same order that occurs in the unsorted array. So, after Round 1, Team B is listed ahead of Team D in Table 3 because in the array being sorted ($F5:$F9) B occurs before D. Teams A and E are similarly affected after round 2, as are Teams A and C after Round 3.

Tables 4 and 5 deal with this problem by first looking at the number of points scored in each placing in Table 3. These results are in Table 4, which shows after round 1 that the teams in places 1 and 2 had both scored 5 points and so should have the same place. Similarly, Table 4 shows that after round 2 the teams in places 4 and 5 both had 4 points, so again should have the same place.

Table 5 corrects the placings by comparing the points values in Table 4 with those in the cells above and below. The rules are:

• If the cell below has equal points but the cell above does not, the current place value in Table 5 has an equals sign added (so for example place 3 would become 3=)
• If the cell above has equal points, the current place value is assigned as the place value of the place above
• If neither applies, the place value is left unchanged

Finally, Table 6 simply combines the team in Table 3 and the place value in Table 5 to provide the final result. Table 6 adds the full information from Table 5 rather than just adding an equals sign (or other indicator of same placing) to avoid the following problem. Suppose Team C is awarded 4 points instead of 3 in Round 2. After 2 rounds Teams A and E would tie on 4 points (as previously), Teams B and C would now both have 7 points (previously C had 6 points) and Team D would have 9 (as previously). With only an equality indicator, Table 6 would show Team D as the sole team in place 1 but would show Teams B, C, A and E as being equally placed with each other rather than two pairs of equally placed teams with separate placings (2= and 4=).

To get from the information in Table 6 to the type of colour-coding used in the OP requires use of VBA. The team name in each cell in the body of Table 6 would be replaced by the corresponding team's colour being given to the cell background.

If VBA is used it might also be more sensible to undertake all the calculations (cumulation of points, sorting, dealing with equal placings) in VBA rather than using Excel's worksheet functions. VBA might be necessary because the SORTBY function used to create Table 3 requires the version of Excel available in Office 365.