Finding the # of instances of a text string when other criteria is met

[jaypoole]

I have a spreadsheet consisting of the columns:

Date,Name of Player,Training Type

The spreadsheet has been sorted by date but there is not one row per date. There could be no rows of data for one particular date, or there could be 20 rows of data for the same date. There is only one date in each cell.

The cells in the second column marked 'Name of Player' contains different combinations of players names. These names are seperated by commas. One cell may have only one players name within, yet another cell may have three players names within it separated by commas. The same players names can appear in any number of cells in this column.

The cells in the third column labelled 'Training Type' contains different training activities that are separated by commas. One cell for instance may contain the following:

awareness, dribbling, heading

while another may contain:

dribbling, heading, tackling, shooting

For each range of dates (covering one month intervals), I need to count the number of times each player has received each type of training. To hopefully give me: -

Player Name Awareness Dribbling Heading Shooting
John Doe 2 1 3 2
Joe Bloggs 1 4 0 1
Jack Frost 3 0 2 4

etc.

I have been struggling with this for ages with ISNUMBER and SUMIF but can only come up with solutions that involve intermediate steps manually using using text to columns etc but I am sure there must be a better way to do this.

Please is anyone able to help..?

 

[GlennUK]

Convert your list to be one entry per cell, i.e. one date, one player, one training type per row. Then you can do analysis very easily ... for example with a PivotTable.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[jaypoole]

I guess that's what I have been doing.. Using 'Text to Columns' to separate multiple players and multiple training types into separate cells.. I just want to see if there is a way of doing it without any extra intermediate steps..

cheers..

Jay