# Rank by date and assigned 'Round/Gameweek' value

#### Gavlor

##### New Member
Hi...I've got myself into a bit of a pickle and could really do with some Excel help!

The attached screen grab is an extract from an Excel file I have that attempts to:
a) Assign the next 'Round' (a.k.a. Gameweek) value to the Home team (Round 1, Round 2, Round 3 etc)
b) Assign the next 'Round' (a.k.a. Gameweek) value to the Away team (Round 1, Round 2, Round 3 etc)

It needs to do this based upon the following factors:
1 - The 'Round' value should be allocated on an ascending basis, based upon the date
2 - 'Round 1' value should be allocated to the earliest date for the team
3 - The 'Round' value should be allocated to a team, irrespective of whether the team is Home or Away
4 - Each 'Round' Home team combination should be unique
5 - Each 'Round' Away team combination should be unique

Within the attached example, the 'AS-IS' table is what I have, the 'TO-BE' table is what I want to achieve. I've highlighted in blue, one team (Bayern Munich) that considers all of the factors described above, by way of an example.

Any help would be greatly appreciated, as I've had a go at this myself using a ton of different formulas and still isn't quite where I want it to be!

#### Attachments

• Excel extract.JPG
169.6 KB · Views: 5
• Excel extract.JPG
169.6 KB · Views: 5

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### jasonb75

##### Well-known Member
Try this in F2, then drag right and fill down.
Excel Formula:
``="round "&COUNTIFS(\$I\$2:\$I\$27,H2,\$E\$2:\$E\$27,"<="&\$E2)+COUNTIFS(\$H\$2:\$H\$27,H2,\$E\$2:\$E\$27,"<="&\$E2)``
Although not likely with your example, it should be noted that the formula will not work correctly if a team has 2 (or more) entries with the same date.

#### Gavlor

##### New Member
Try this in F2, then drag right and fill down.
Excel Formula:
``="round "&COUNTIFS(\$I\$2:\$I\$27,H2,\$E\$2:\$E\$27,"<="&\$E2)+COUNTIFS(\$H\$2:\$H\$27,H2,\$E\$2:\$E\$27,"<="&\$E2)``
Although not likely with your example, it should be noted that the formula will not work correctly if a team has 2 (or more) entries with the same date.
Amazing Jason!!!! I've only tried it against one subset of data so far, but it looks like it's work . You have no idea how much time/effort you've just saved me, absolutely brilliant. I really can't thank you enough

Replies
0
Views
294
Replies
1
Views
54
Replies
9
Views
136
Replies
0
Views
201
Replies
3
Views
269

1,126,997
Messages
5,622,082
Members
415,875
Latest member
Tarali

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back