Index/Match & Mode?

**WWSL14** · 06-20-2013, 02:42 PM

I'm not sure if "MODE" is available with a pivot table, but this is what I have.

Please Login or Register  to view this content.

Column A is a list of "player id's"
Column B is a list of years
Column C is the team id that player played for.

What I did was create a pivot table with this information. (I have attached the file)

What I'm looking for is to be able to find the most common number in Column C for each unique value in Column A.

Book1.xlsx

**newdoverman** · 06-20-2013, 04:03 PM

This indicates the maximum count of Team ID's for each player ID which indicates the longest period spent with one team.

Enter this in D8 of sheet2 and copy down

Formula:

Please Login or Register  to view this content.

Enter this with Ctrl+Shift+Enter in E8 and copy down

Formula:

Please Login or Register  to view this content.

I had the results included in your file but the uploader wouldn't work.

**Ace_XL** · 06-20-2013, 04:04 PM

In Cell D4 on Sheet 2, use this array formula

=IFERROR(MODE(IF(players_career_batting_stats.cs!$A$8:$A$9999=A4,players_career_batting_stats.cs!$C$8:$C$9999)),"")

Confirm with Ctrl+Shift+Enter and not just Enter

copy down