Tracking player participation throughout season

John_Wh

Board Regular
Joined
Feb 26, 2013
Messages
64
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]http://i64.tinypic.com/nn7ehj.jpg[/IMG]
 
Last edited:

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

John_Wh

Board Regular
Joined
Feb 26, 2013
Messages
64
I know I can conditional format for color once I get the "X"'s in the cells
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Joe</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Sam</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">John</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Joe</td><td style="text-align: right;;">X</td><td style="text-align: right;;">X</td><td style="text-align: right;;">X</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Billy</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Sam</td><td style="text-align: right;;">X</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">X</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Joe</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">John</td><td style="text-align: right;;">X</td><td style="text-align: right;;">X</td><td style="text-align: right;;">X</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">John</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Billy</td><td style="text-align: right;;">X</td><td style="text-align: right;;">X</td><td style="text-align: right;;"></td><td style="text-align: right;;">X</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Billy</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Tim</td><td style="text-align: right;;"></td><td style="text-align: right;;">X</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Tim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Willy</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">X</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Joe</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Don</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">X</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">John</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Tom</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">X</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Willy</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Frank</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">X</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Don</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Tom</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Frank</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Sam</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Billy</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">All</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G4</th><td style="text-align:left">=INDEX(<font color="Blue">$D$2:$D$17,MATCH(<font color="Red">0,INDEX(<font color="Green">COUNTIF(<font color="Purple">$G$3:G3,$D$2:$D$17</font>),0</font>),0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H4</th><td style="text-align:left">=IF(<font color="Blue">COUNTIFS(<font color="Red">$A$2:$A$17,H$3,$D$2:$D$17,$G4</font>)>=1,"X",""</font>)</td></tr></tbody></table></td></tr></table><br />
 

John_Wh

Board Regular
Joined
Feb 26, 2013
Messages
64
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How did you modify the formulae?
 

John_Wh

Board Regular
Joined
Feb 26, 2013
Messages
64
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]http://i63.tinypic.com/2z8qfkx.jpg[/IMG]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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_Wh

Board Regular
Joined
Feb 26, 2013
Messages
64
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,317
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top