Historical Stats

[mpenkala]

Hi there. I'm trying to do the following. I have 4 teams with 9 players on
each team. I want to keep track of when someone scores. I also want to keep
track of the last time they scored.

So...

Col A lists the day (simply 1-31, no month needed).
Col B I list the player that scored that day. Only 1 player scores per day.
Col F is Team 1. When a player from team 1 scores, It imports to the first
empty row in Col F.

This is were I'm having problems. How do I get the cells in Col F to
examine each day and see if someone from Team 1 has scored. And if they have
scored, to enter the day they scored on the next available row.

I hope this makes sense!

Thanks,
Matt

 

[Luke M]

You didn't say how you know which player is on which team? Does column B say
"Team 1"/"Team 2"? Nevertheless, its simple to setup the correlation, you can
either have a seperate column, or include a LOOKUP function (see XL help
file).

For now, let's assume in column B you have "Team 1" through "Team 4" as
possibilities listed, starting on row 2. In F2:

=IF(COUNTIF($B$2:$B$101,"Team
1")>=ROW()-ROW($A$2)+1,INDIRECT(ADDRESS(LARGE(($B$2:$B$101="Team
1")*ROW($B$2:$B$101),COUNTIF($B$2:$B$101,"Team
1")-ROW()+2),COLUMN()-COLUMN($F$2)+1)),"")

Hopefully this gets you pointed in direction you need to go. You can adjust
ranges/references as needed.