Closed Thread
Results 1 to 14 of 14

'Fixture List' table needed

  1. #1
    Forum Contributor
    Join Date
    08-21-2012
    Location
    London
    MS-Off Ver
    Office 365 MAC
    Posts
    102

    'Fixture List' table needed

    Hello there and thank you for taking the time to read my post.
    I am looking for some assistance with a 'fixture list' type table for job comparisons
    Please see the attached Excel example which I have manually put together. The list of Jobs could be in the 100's if not thousands and I, therefore, need to automate this part of the process!

    The simplest way to describe my need is like a football league arrangement. There is a list of teams and I need to create a table of Team A versus Team B. In my case, they are jobs not teams.

    PART 1
    I need to create the 'fixture list' initially for each job to appear in the fixture table. This can then be used to manually enter a result. (I do not need help with this part)

    PART 2
    It is likely that I will need to compare Job A with Job B, C, D etc but only once and not in a home and away type situation. For example, I do not need Job A v Job B and then Job B v Job A

    Ideally I would like the overall ordering to be completely random, so not AvB, AvC, AvD etc.... all mixed up such as AvB, DvF, GvC, EvA etc.

    I am happy to have helper columns because once random generator has been done I will cut and paste the values into a new sheet to 'fix' the output.

    I hope all that makes sense, I find it very difficult to explain what I am wanting to achieve, however I hope the attached explains a little better.

    Thank you to anyone who can offer me help, I do appreciate it.
    Last edited by AliGW; 06-28-2020 at 05:59 AM. Reason: Irrelevant section of title removed: this is a HELP forum!!!
    Learner, making mistakes, asking daft questions.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,209

    Re: 'Fixture List' table needed

    I don't understand this.
    here is my guess.

    H6:I10

    =INDEX(Table1[Description],AGGREGATE(15,6,Table1[JobRef]/NOT(COUNTIF($H$5:$I5,Table1[Description]))/(Table1[Description]<>G6),RANDBETWEEN(1,MAX(Table1[JobRef])-ROWS(G$6:G6)*2+COLUMNS(H6:$I6))))

    Press Ctrl+Enter

    Press F9 to random

  3. #3
    Forum Contributor
    Join Date
    08-21-2012
    Location
    London
    MS-Off Ver
    Office 365 MAC
    Posts
    102

    Re: 'Fixture List' table needed

    Thank you, considering you did not understand you have worked the solution very well, I appreciate that.

    The solution is great for Part 1 of my requirement, each description appears only once in the table. How can I answer the need of Part 2,extend it so that each description appears against each other once?

    For example AvC, AvD, etc, BvD, BvE etc?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,472

    Re: 'Fixture List' table needed

    It's such a shame you have a Mac! I have a ready-made PowerQuery solution that would have been perfect.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Contributor
    Join Date
    08-21-2012
    Location
    London
    MS-Off Ver
    Office 365 MAC
    Posts
    102

    Re: 'Fixture List' table needed

    @AliGW - I can run windows on my mac too in order to do the generation if required?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,472

    Re: 'Fixture List' table needed

    No, it's the version of Excel (Excel for Mac) you have that doesn't support it - sorry.

  7. #7
    Forum Contributor
    Join Date
    08-21-2012
    Location
    London
    MS-Off Ver
    Office 365 MAC
    Posts
    102

    Re: 'Fixture List' table needed

    Let me explain. I have a partition on my MAc which basically means I run windows. On there I run Excel for Windows, not for MAC. I have to use this method for creating forms for example.
    If you are able to share your solution that would be most helpful.
    Thank you

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,472

    Re: 'Fixture List' table needed

    OK - in that case, I will move your thread back and you need to update your forum user profile!!!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,472

    Re: 'Fixture List' table needed

    So, you need to load the table you have to PQ and first create from it a list of jobs, which you load to connection only. Then this M Code:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,209

    Re: 'Fixture List' table needed

    Please try

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,472

    Re: 'Fixture List' table needed

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

  12. #12
    Forum Contributor
    Join Date
    08-21-2012
    Location
    London
    MS-Off Ver
    Office 365 MAC
    Posts
    102

    Re: 'Fixture List' table needed

    Quote Originally Posted by Bo_Ry View Post
    Please try

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thank you. This helps a lot. I appreciate your time.

  13. #13
    Forum Contributor
    Join Date
    08-21-2012
    Location
    London
    MS-Off Ver
    Office 365 MAC
    Posts
    102

    Re: 'Fixture List' table needed

    Quote Originally Posted by AliGW View Post
    OK - in that case, I will move your thread back and you need to update your forum user profile!!!
    I don't see the need to update my user profile. I am a mac user and that is my main operating system. Mac users have the ability to run windows and therefore windows software as an alternative where required. Since you mentioned PQ was the solution, I mentioned the ability.

  14. #14
    Forum Contributor
    Join Date
    08-21-2012
    Location
    London
    MS-Off Ver
    Office 365 MAC
    Posts
    102

    Re: 'Fixture List' table needed

    Quote Originally Posted by AliGW View Post
    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    "Brought to your attention"? How very Machiavellian!
    If it is such a problem then please delete the post.
    On the other forum there was no ability to upload a workbook, hence why I reverted to this forum.
    If the forums are not connected I don't see what the issue is but to be quite honest if this is the sort of response then I wont bother in future.

    I have a solution from Bo_Ry that works and without any fuss. I will let you get back to your moderating and the lofty position that projects you to.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Some questions about the fixture list creator
    By albertpyyang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-19-2017, 02:07 PM
  2. [SOLVED] How to calculate lamps per fixture based on lamp type & lamps per fixture?
    By Sandcastle in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-27-2015, 04:09 PM
  3. Small Fixture List
    By wayne0881 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-28-2013, 01:15 PM
  4. [SOLVED] Random Grouping/Fixture List??
    By Paul Mc in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-22-2006, 09:35 AM
  5. Fixture list in excel?
    By Paul Mc in forum Excel General
    Replies: 0
    Last Post: 03-22-2006, 09:35 AM
  6. football formula Fixture table help
    By Paul T in forum Excel General
    Replies: 2
    Last Post: 09-04-2005, 03:05 PM
  7. [SOLVED] HOW DO I SET UP A 12 TEAM FIXTURE TABLE PLAYING EACH OTHER ONCE
    By BRIAN MURPHY in forum Excel General
    Replies: 0
    Last Post: 05-30-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1