+ Reply to Thread
Results 1 to 7 of 7

Lookup

  1. #1
    Registered User
    Join Date
    12-27-2009
    Location
    Karachi
    MS-Off Ver
    Excel 2007
    Posts
    4

    Lookup

    Hello All,

    I am trying to generate a league schedule. I have drawn a chart of all matches which will by played but i am unable to convert that chart into columns. Kindly give a formula that will convert that charge into columns. The chart is attached with this message.

    The team names are displayed in first row and first column. When the intersection of these is "H" the team name in column is to be displayed in home team column, when intersection is "A" the team name in first column is displayed in away team column.

    Your help is required.

    Regards
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Lookup Problem

    There's nothing stipulating the order in which the fixtures are to be listed... on that basis perhaps simply:

    Please Login or Register  to view this content.
    there will be other variants of the same but the above should generate the 272 fixtures as requested (with formulae).

  3. #3
    Registered User
    Join Date
    12-27-2009
    Location
    Karachi
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Lookup Problem

    First of all thanks for your time and response.

    Well there are two problems;

    1. The home and away matches are not differentiated as per table. You see in the table there are number of "H" and "A". You see i wanted that If "H" appears in from of Column A Teams then it should be displayed in Column V otherwise it should be displayed in Column Y.

    2. The second problem is that Matches are duplicated. For example NAR has already Played with STE but when STE matched are displayed in column V NAR is again selected. I actually want one match with each team not two matches.

    Regards

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Lookup

    I had assumed each side would play each other side home and away - obviously this is not so.

    I'm not around much today so I'm afraid I have not had the time to come up with an elegant formula solution to this however the attached does (I believe) generate the 136 fixtures you require albeit with use of helpers.

    Invariably you will get more elegant alternatives but I hope the attached gives you some pointers if nothing else.

    EDIT: 07:51 UK time attachment reloaded (prior version missing formulae in AB)
    Attached Files Attached Files
    Last edited by DonkeyOte; 12-30-2009 at 03:51 AM. Reason: updated attachment

  5. #5
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Lookup

    Alternative without helper columns, using matrix formula.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-27-2009
    Location
    Karachi
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Lookup

    Thank you both.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Lookup

    Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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