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

[PosiJoel]

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.

[AlKey]

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

Please see attached file

[Ron Coderre]

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?

[PosiJoel]

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.

[Ron Coderre]

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.

[Ron Coderre]

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.

[AlKey]

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)))

[Ron Coderre]

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! :\ )

[PosiJoel]

Amazing Ron thanks a ton.

Perfect solution.