Mode type function for text array

[Scott26]

Hi. I know there are many many similar threads to this, but I can't get any of them to work for me. I don't know a lot about VBA which is a shame. I'm trying to get to grips with it, because I'm certain it'll make my life easier. In the meantime, maybe one (or more) of you guys can.

I basically want a formula that replicates the mode function for text values.

I have a spreadsheet with lists of who has scored in football matches for a number of different teams in a league, in each week. So for week 1, A3 to A22 are the team names. And the player names are in B3 to E22 (but could potentially stretch across to say J22 if there were a lot of goals).

Each week has the same format. Team names in the A column, Scorer names in the B through J columns. Week 2 is rows 26 to 45. Week 3 is 49 to 68. And so forth through to week 38, rows 854 to 873.

What I want is a formula for another sheet, which says that Team A's top scorer is Player A, and the second top scorer is Player B. And Team B's top scorer is Player C and the second top scorer is Player D. So Team A's top scorer can be read on the 'overview sheet' using the information from the 'scorers sheet'.

I'm using MS Excel 2007, on Windows 7.

Any useful thoughts or solutions would be much appreciated, thanks.

 

[Scott26]

The problem i'm having is that

=INDIRECT("R"&SUBSTITUTE(MIN(IF(COUNTIF(Goals!B3:J22,Goals!B3:J22)=MAX(COUNTIF(Goals!B3:J22,Goals!B3:J22)),ROW(Goals!B3:B22)+COLUMN(Goals!B3:J3)/10)),".","C"),FALSE)

doesn't work, I think because I have blank fields. And having non-blank fields obviously returns "" as the most frequent scorer. And this only returns the top scorer of the week, regardless of team. I want the top scorer of the team, over the whole season.

 

[Scott26]

Or I can do a similar thing with

=INDIRECT("R"&SUBSTITUTE(MIN(IF(COUNTIF(Goals!$B3:$J3,Goals!$B3:$J3)=MAX(COUNTIF(Goals!$B3:$J3,Goals!$B3:$J3)),ROW(Goals!$B3)+COLUMN(Goals!$B3:$J3)/10)),".","C"),FALSE)

to find the top scorer for each team for that week. But I can't extrapolate from this to find the top scorer for the team for the season.

 

[Scott26]

-Bump-