+ Reply to Thread
Results 1 to 9 of 9

Returning most frequest string, ignoring specific strings, with an offset

  1. #1
    Registered User
    Join Date
    10-17-2012
    Location
    denver, co
    MS-Off Ver
    Excel Mac 2011
    Posts
    15

    Returning most frequest string, ignoring specific strings, with an offset

    I'm putting together a spreadsheet for a forum game, and want to track each users favorite player (by who they select to score a game winning goal most often).

    I have a sheet titled 'GWG Scorer' With each users selection, and a 'Favorite Player' sheet to track who each user chooses more often.
    If they didn't play a particular game then I enter "DNP", or if they guess the game goes to a shootout, I enter "SHOOTOUT". I want to ignore both of those values when looking for the most common.

    Attached is a small example of what I'd like. Just trying to figure out the formula to put in the Favorite Player sheet for B1, B2 & B3.
    FavoritePlayerExample.xlsx

    I'm also struggling to figure out the Offset function in all of this as well.

    currently I'm using this formula (most of which I siphoned from this site):

    {=IFERROR(INDEX(OFFSET('GWG Scorer'!B$2:B$83,,ROW()-2),MODE(MATCH('GWG Scorer'!B$2:B$83,'GWG Scorer'!B$2:B$83,0)+{0,0})),"")}

    I'm definitely doing something wrong here, and I would also like to add an IF statement in there somewhere for ignoring "DNP" and "SHOOTOUT".
    Any help would be greatly appreciated.
    Let me know if you need more reference.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Returning most frequest string, ignoring specific strings, with an offset

    Your formula works just fine. You just need to adjust ranges and ROW()-2)

    Please see attached file
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Returning most frequest string, ignoring specific strings, with an offset

    Using your posted workbook...
    This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER) returns the first occurring most used player:
    Please Login or Register  to view this content.
    Change the referenced ranges to suit each User

    Is that something you can work with?
    Last edited by Ron Coderre; 09-19-2014 at 01:20 PM.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    10-17-2012
    Location
    denver, co
    MS-Off Ver
    Excel Mac 2011
    Posts
    15

    Re: Returning most frequest string, ignoring specific strings, with an offset

    This is helpful Ron thank you.

    I'm still trying to use =OFFSET to be able to drag the formula down while still using the correct horizontal references in the GWG Scorer sheet. I actually am going to have 60-some users. And it seems like it would be extremely tedious to change all of those manually.

    Maybe offset is the wrong function, I'm not sure.

  5. #5
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Returning most frequest string, ignoring specific strings, with an offset

    Ok...using your posted workbook, this array formula will automatically reference the correct columns when copied down:
    Please Login or Register  to view this content.
    or...if the Users on the GWG Scorer sheet may be in different order, this array formula finds the Col_A name in the headers and adjusts the references:
    Please Login or Register  to view this content.
    I hope that helps.

  6. #6
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Returning most frequest string, ignoring specific strings, with an offset

    One more thing...If you'll be copying the formulas down where will be no activity:
    Wrap either formula in the IFERROR function.
    This variation displays empty text if there would otherwise be an error:
    Please Login or Register  to view this content.

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Returning most frequest string, ignoring specific strings, with an offset

    Maybe this time

    =INDEX(INDIRECT("'GWG Scorer'!"&CHAR(65+(ROWS($A$1:A1)))&"2:"&CHAR(65+(ROWS($A$1:A1)))&"9"),MODE(MATCH(INDIRECT("'GWG Scorer'!"&CHAR(65+(ROWS($A$1:A1)))&"2:"&CHAR(65+(ROWS($A$1:A1)))&"9"),INDIRECT("'GWG Scorer'!"&CHAR(65+(ROWS($A$1:A1)))&"2:"&CHAR(65+(ROWS($A$1:A1)))&"9"),0)))

  8. #8
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Returning most frequest string, ignoring specific strings, with an offset

    That formula includes no test to avoid "DNP" or "SHOOTOUT".
    Consequently, if one of those excluded values occurs the most times...it will display.
    To see that...add another DNP to User2's data

    (It's the excluded items that really muck up the solution! :\ )

  9. #9
    Registered User
    Join Date
    10-17-2012
    Location
    denver, co
    MS-Off Ver
    Excel Mac 2011
    Posts
    15

    Re: Returning most frequest string, ignoring specific strings, with an offset

    Amazing Ron thanks a ton.

    Perfect solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 06-04-2014, 04:12 PM
  2. [SOLVED] FIND Function; looking for an array of strings and returning found string value
    By loloduane in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-05-2014, 04:11 AM
  3. Replies: 7
    Last Post: 03-19-2012, 02:47 PM
  4. Search for any Text String in specific range and copy + offset + move
    By FN2010 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-05-2010, 07:04 PM
  5. [SOLVED] can you find specific text in a string ignoring any other text
    By chriscp in forum Excel General
    Replies: 1
    Last Post: 09-18-2005, 05: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