Tracking player participation throughout season

[John_Whin]

I've got a roster report with game number in first column (A) and player name in 4th column (D). Would like to represent the season in another way. Want the list of players on the team in one column and the game # in the top row. With the corresponding cells either having an X in them or possibly a color.

How do I load the table? This is small example, I've got 82 games and a roster list of 30+ players with about 20 in every game.

[/IMG]

 

[John_Whin]

I know I can conditional format for color once I get the "X"'s in the cells

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
2	1			Joe
3	1			Sam				1	2	3	4
4	1			John			Joe	X	X	X
5	1			Billy			Sam	X			X
6	2			Joe			John	X	X	X
7	2			John			Billy	X	X		X
8	2			Billy			Tim		X
9	2			Tim			Willy			X
10	3			Joe			Don			X
11	3			John			Tom				X
12	3			Willy			Frank				X
13	3			Don
14	4			Tom
15	4			Frank
16	4			Sam
17	4			Billy
All

Cell Formulas
Range	Formula
G4	=INDEX($D$2:$D$17,MATCH(0,INDEX(COUNTIF($G$3:G3,$D$2:$D$17),0),0))
H4	=IF(COUNTIFS($A$2:$A$17,H$3,$D$2:$D$17,$G4)>=1,"X","")

 

[John_Whin]

When I applied it to the sample data on my spreadsheet it worked well, but when I plugged it into my actual data page it didn't. the cell that corresponds to G4 in your example is actually P3, H4 is actually Q3.

 

[Fluff]

How did you modify the formulae?

 

[John_Whin]

I didn't. I just plugged

=INDEX($D$2:$D$17,MATCH(0,INDEX(COUNTIF($G$3:G3,$D$2:$D$17),0),0)) into P3

=IF(COUNTIFS($A$2:$A$17,H$3,$D$2:$D$17,$G4)>=1,"X","") into Q3

here is the actual sheet

[/IMG]

 

[Fluff]

You need to change the cell references
=INDEX($D$2:$D$17,MATCH(0,INDEX(COUNTIF($P$2:P2,$D$2:$D$17),0),0))
=IF(COUNTIFS($A$2:$A$17,Q$2,$D$2:$D$17,$P3)>=1,"X","")

You will also need to change the 17 to reflect the last row of data

 

[John_Whin]

Changed the 17 to 1600 in both formulas and it's perfect. Fluff, you've been helpful numerous times wish I could buy you multiple rounds of your favorite beverage!

Till next time ... Cheers

 

[Fluff]

Glad to help & thanks for the feedback