Rank by date and assigned 'Round/Gameweek' value

Gavlor

New Member
Joined
Feb 11, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
    Excel extract.JPG
    169.6 KB · Views: 5
  • Excel extract.JPG
    Excel extract.JPG
    169.6 KB · Views: 5

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,405
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 11, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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 (y)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top