+ Reply to Thread
Results 1 to 6 of 6

Index/Match/...? Help...

  1. 09-06-2016, 04:54 PM #1
    chrisdavis97
    chrisdavis97 is offline
    Registered User
    Join Date
    09-06-2016
    Location
    Fort Collins CO
    MS-Off Ver
    2010
    Posts
    5

    Index/Match/...? Help...

    Hi everyone, first post.
    I've been working on a spreadsheet for a Football pool, and with the help of some threads here and from google, I've gotten pretty far with it the last couple years (slow progress, I know). I have it calculating a ton of stuff across many tabs, and it's all working as of now. But I am now taking over the role of running the pool instead of just participating. So now I want to add the ability to count wins and losses and put them into another tab to show their position with a yearly total. Some of it may sound confusing, so I will try to give examples.

    . . . . . . . . Week 1 . .
    Broncos . . . . W . . . .
    Panthers. . . . .L . . . .

    Giants . . . . . .W. . . .
    Cowboys . . . . L . . . .

    I pick Broncos and Giants to win, so I'm 2-0.
    James picked Broncos and Cowboys to win, so he's 1-1.
    Rick picks Panthers and Cowboys to win, so he's 0-2.

    If I have one tab dedicated to show teams down the left, and the week number across the top, and the data for these are "W" and "L", added up at the end, and I have another tab that shows "A3" vs "B3", and our pick is "C3, D3, E3, etc", with our names above in "C2, D2, E2, etc", what is the easiest way (or best way) to gather data into a new tab that will search our picks and compare the "W"s and "L"s to represent a 0-0 display?

    I can upload if you prefer to take a look.
    Reply With QuoteRegister To Reply
  2. 09-06-2016, 05:05 PM #2
    Olly
    Olly is offline
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Index/Match/...? Help...

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...
    Reply With QuoteRegister To Reply
  3. 09-06-2016, 05:33 PM #3
    chrisdavis97
    chrisdavis97 is offline
    Registered User
    Join Date
    09-06-2016
    Location
    Fort Collins CO
    MS-Off Ver
    2010
    Posts
    5

    Re: Index/Match/...? Help...

    Thanks, I have attached the file.
    I filled in the picked teams, to help with what I'm looking for.
    You can ignore the "BYE" messages in the 'TEAMS' tab. Those are there because I need to finish the other 16 weeks of the Schedule.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 09-08-2016, 12:36 PM #4
    chrisdavis97
    chrisdavis97 is offline
    Registered User
    Join Date
    09-06-2016
    Location
    Fort Collins CO
    MS-Off Ver
    2010
    Posts
    5

    Re: Index/Match/...? Help...

    I think I have a long/roundabout solution to this, but I'm running into another error that I didn't catch before.
    To cover my first question, this is what I did...
    =IF(ISBLANK(G3),"",IF((INDEX(TEAMS!$B$2:$B$33,MATCH(G3,
    TEAMS!$A$2:$A$33,0)))="T","TIE",INDEX(TEAMS!$B$2:$B$33,MATCH(G3,
    TEAMS!$A$2:$A$33,0))="W"))
    After I put a W or L next to the winner or loser, this function would return TRUE or FALSE, unless it was a TIE, when I would put a "T" in. I copied down for 17 cells to give answers to all of the games.
    Then, I would add up the TRUE/FALSE/TIE words...
    =IF((COUNTIF(G38:G53,"TIE"))=0,(COUNTIF(G38:G53,"TRUE")&"-"&COUNTIF(G38:G53,"FALSE")),(COUNTIF(G38:G53,"TRUE")&"-"&COUNTIF(G38:G53,"FALSE")&"-"&COUNTIF(G38:G53,"TIE")))
    Then, I just opened a new tab and pulled the cell info from G54 into the new tab. The next step got a little more complicated, as I had to add up ALL weeks into a total...
    =IF(((COUNTIF('Week 1'!E38:E53,"TIE"))+(COUNTIF('Week 2'!E38:E53,"TIE"))+(COUNTIF('Week 3'!E38:E53,"TIE"))+(COUNTIF('Week 4'!E38:E53,"TIE"))+(COUNTIF('Week 5'!E38:E53,"TIE"))+(COUNTIF('Week 6'!E38:E53,"TIE"))+(COUNTIF('Week 7'!E38:E53,"TIE"))+(COUNTIF('Week 8'!E38:E53,"TIE"))+(COUNTIF('Week 9'!E38:E53,"TIE"))+(COUNTIF('Week 10'!E38:E53,"TIE"))+(COUNTIF('Week 11'!E38:E53,"TIE"))+(COUNTIF('Week 12'!E38:E53,"TIE"))+(COUNTIF('Week 13'!E38:E53,"TIE"))+(COUNTIF('Week 14'!E38:E53,"TIE"))+(COUNTIF('Week 15'!E38:E53,"TIE"))+(COUNTIF('Week 16'!E38:E53,"TIE"))+(COUNTIF('Week 17'!E38:E53,"TIE")))=0,((COUNTIF('Week 1'!E38:E53,"TRUE"))+(COUNTIF('Week 2'!E38:E53,"TRUE"))+(COUNTIF('Week 3'!E38:E53,"TRUE"))+(COUNTIF('Week 4'!E38:E53,"TRUE"))+(COUNTIF('Week 5'!E38:E53,"TRUE"))+(COUNTIF('Week 6'!E38:E53,"TRUE"))+(COUNTIF('Week 7'!E38:E53,"TRUE"))+(COUNTIF('Week 8'!E38:E53,"TRUE"))+(COUNTIF('Week 9'!E38:E53,"TRUE"))+(COUNTIF('Week 10'!E38:E53,"TRUE"))+(COUNTIF('Week 11'!E38:E53,"TRUE"))+(COUNTIF('Week 12'!E38:E53,"TRUE"))+(COUNTIF('Week 13'!E38:E53,"TRUE"))+(COUNTIF('Week 14'!E38:E53,"TRUE"))+(COUNTIF('Week 15'!E38:E53,"TRUE"))+(COUNTIF('Week 16'!E38:E53,"TRUE"))+(COUNTIF('Week 17'!E38:E53,"TRUE")))&"-"&((COUNTIF('Week 1'!E38:E53,"FALSE"))+(COUNTIF('Week 2'!E38:E53,"FALSE"))+(COUNTIF('Week 3'!E38:E53,"FALSE"))+(COUNTIF('Week 4'!E38:E53,"FALSE"))+(COUNTIF('Week 5'!E38:E53,"FALSE"))+(COUNTIF('Week 6'!E38:E53,"FALSE"))+(COUNTIF('Week 7'!E38:E53,"FALSE"))+(COUNTIF('Week 8'!E38:E53,"FALSE"))+(COUNTIF('Week 9'!E38:E53,"FALSE"))+(COUNTIF('Week 10'!E38:E53,"FALSE"))+(COUNTIF('Week 11'!E38:E53,"FALSE"))+(COUNTIF('Week 12'!E38:E53,"FALSE"))+(COUNTIF('Week 13'!E38:E53,"FALSE"))+(COUNTIF('Week 14'!E38:E53,"FALSE"))+(COUNTIF('Week 15'!E38:E53,"FALSE"))+(COUNTIF('Week 16'!E38:E53,"FALSE"))+(COUNTIF('Week 17'!E38:E53,"FALSE"))),((COUNTIF('Week 1'!E38:E53,"TRUE"))+(COUNTIF('Week 2'!E38:E53,"TRUE"))+(COUNTIF('Week 3'!E38:E53,"TRUE"))+(COUNTIF('Week 4'!E38:E53,"TRUE"))+(COUNTIF('Week 5'!E38:E53,"TRUE"))+(COUNTIF('Week 6'!E38:E53,"TRUE"))+(COUNTIF('Week 7'!E38:E53,"TRUE"))+(COUNTIF('Week 8'!E38:E53,"TRUE"))+(COUNTIF('Week 9'!E38:E53,"TRUE"))+(COUNTIF('Week 10'!E38:E53,"TRUE"))+(COUNTIF('Week 11'!E38:E53,"TRUE"))+(COUNTIF('Week 12'!E38:E53,"TRUE"))+(COUNTIF('Week 13'!E38:E53,"TRUE"))+(COUNTIF('Week 14'!E38:E53,"TRUE"))+(COUNTIF('Week 15'!E38:E53,"TRUE"))+(COUNTIF('Week 16'!E38:E53,"TRUE"))+(COUNTIF('Week 17'!E38:E53,"TRUE")))&"-"&((COUNTIF('Week 1'!E38:E53,"FALSE"))+(COUNTIF('Week 2'!E38:E53,"FALSE"))+(COUNTIF('Week 3'!E38:E53,"FALSE"))+(COUNTIF('Week 4'!E38:E53,"FALSE"))+(COUNTIF('Week 5'!E38:E53,"FALSE"))+(COUNTIF('Week 6'!E38:E53,"FALSE"))+(COUNTIF('Week 7'!E38:E53,"FALSE"))+(COUNTIF('Week 8'!E38:E53,"FALSE"))+(COUNTIF('Week 9'!E38:E53,"FALSE"))+(COUNTIF('Week 10'!E38:E53,"FALSE"))+(COUNTIF('Week 11'!E38:E53,"FALSE"))+(COUNTIF('Week 12'!E38:E53,"FALSE"))+(COUNTIF('Week 13'!E38:E53,"FALSE"))+(COUNTIF('Week 14'!E38:E53,"FALSE"))+(COUNTIF('Week 15'!E38:E53,"FALSE"))+(COUNTIF('Week 16'!E38:E53,"FALSE"))+(COUNTIF('Week 17'!E38:E53,"FALSE")))&"-"&((COUNTIF('Week 1'!E38:E53,"TIE"))+(COUNTIF('Week 2'!E38:E53,"TIE"))+(COUNTIF('Week 3'!E38:E53,"TIE"))+(COUNTIF('Week 4'!E38:E53,"TIE"))+(COUNTIF('Week 5'!E38:E53,"TIE"))+(COUNTIF('Week 6'!E38:E53,"TIE"))+(COUNTIF('Week 7'!E38:E53,"TIE"))+(COUNTIF('Week 8'!E38:E53,"TIE"))+(COUNTIF('Week 9'!E38:E53,"TIE"))+(COUNTIF('Week 10'!E38:E53,"TIE"))+(COUNTIF('Week 11'!E38:E53,"TIE"))+(COUNTIF('Week 12'!E38:E53,"TIE"))+(COUNTIF('Week 13'!E38:E53,"TIE"))+(COUNTIF('Week 14'!E38:E53,"TIE"))+(COUNTIF('Week 15'!E38:E53,"TIE"))+(COUNTIF('Week 16'!E38:E53,"TIE"))+(COUNTIF('Week 17'!E38:E53,"TIE"))))
    There's probably an easier way to this, but it's working.
    Last edited by chrisdavis97; 09-08-2016 at 05:03 PM.
    Reply With QuoteRegister To Reply
  5. 09-08-2016, 12:52 PM #5
    chrisdavis97
    chrisdavis97 is offline
    Registered User
    Join Date
    09-06-2016
    Location
    Fort Collins CO
    MS-Off Ver
    2010
    Posts
    5

    Re: Index/Match/...? Help...

    The issue I'm running in now is a circular reference.
    Week 1 Tab... 'A4' pulls INDEX/MATCH data from 'WLT' tab. In the WLT tab, I have a total Win/Loss/Tie for each team.
    It works, as long as I only use Week 1. As soon as I start typing Teams into Week 2, it gives me a circular error.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 09-08-2016, 09:53 PM #6
    chrisdavis97
    chrisdavis97 is offline
    Registered User
    Join Date
    09-06-2016
    Location
    Fort Collins CO
    MS-Off Ver
    2010
    Posts
    5

    Re: Index/Match/...? Help...

    Okay, I changed the formula options, to accept the circular reference, and everything is working.

    You may close this, unless anyone see's something that may simplify some of my formulas.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  2. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  3. Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail
    By sfike in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. [SOLVED] Index Match from a cell populated from index match
    By MarcLewis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-15-2013, 05:30 AM
  5. Index, match, dynamic and connected without sorting helper column non-adjacent index match
    By Underling in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  6. [SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings
    By bkwins in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  7. Index/Match/Match. Formula looking veritcal and horizontal headings to find a match
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-29-2012, 08:51 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1