Rank by date and assigned 'Round/Gameweek' value

[Gavlor]

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!

 

[jasonb75]

Try this in F2, then drag right and fill down.

="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]

Try this in F2, then drag right and fill down.

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