Lookup Match and result set

[dsharpe1977]

I have a roster of users who have been assigned a specific role (columns) for a number of different dates (Row).
What I'd like to get is a full list of all users, and their role and date for every entry in the roster.
I need to provide each user with their 'personal task diary' and I cannot find a way to do this.

Here are the column titles (row 1) (Comma Separated):
Date,Warm Up,Batting,Bowling/Fielding,Lockup 1,Lockup 2,Lunch 1,Lunch 2,Bar 1 Bar 2,Junior Training 1,Junior Training 2,Junior Training 3,Junior Training 4

Data:
"Tuesday, 5 October 2010",Ben Banks-Smith,Tim Sharpe,Jordan Anthony,,,,,,,Tom Bach,Tim Bach,Marty Lynch,Alex King,,,,,,,,,,,,
"Thursday, 7 October 2010",Alex King,Tom Casha,Dan Burt,Matthew Francis,Tom Bach,,,,,Dan Sharpe,Andrew Murray,Peter Morrison,Tom Bach,,,,,,,,,,,,
"Saturday, 9 October 2010",,,,Matthew Francis,Tom Bach,Justin Murray,Eugene Fitzwilliam,Lawrence Colman,Sam Fairbrother,,,,,,,,,,,,,,,,
"Tuesday, 12 October 2010",Dom Doyle,Dan Sharpe,Sam Fairbrother,,,,,,,Alex King,Brad Craine,Andrew Krohn,Tom Bach,,,,,,,,,,,,
"Thursday, 14 October 2010",Tom Bach,Lawrence Colman,Dan Sharpe,Luke D'Astoli,Tom Sandilands,,,,,Sam Bach,Dan O'Connell,Lawrence Coleman,Andrew Murray,,,,,,,,,,,,
"Saturday, 16 October 2010",,,,Luke D'Astoli,Tom Sandilands,Tom Casha,Peter Morrison,Rohan McDowall,Adam D'Addazio,,,,,,,,,,,,,,,,
"Tuesday, 19 October 2010",Steve O'Connell,Adam D'Addazio,Martin Lynch,,,,,,,Tom Casha,Adam D'Addazio,Alex King,Tom Bach,,,,,,,,,,,,
"Thursday, 21 October 2010",Luke D'Astoli,Peter Lynch,Fabian D'Addazio,Dan Burt,Ben Banks-Smith,,,,,Andrew Gordon,Ben Banks-Smith,Dan Sharpe,Andrew Murray,,,,,,,,,,,,
"Saturday, 23 October 2010",,,,Dan Burt,Ben Banks-Smith,Sam Fairbrother,Dan O'Connell,Brendan David,Dom Doyle,,,,,,,,,,,,,,,,
"Tuesday, 26 October 2010",Ben Banks-Smith,Beau Coyle,Peter Morrison,,,,,,,Jack Krohn,Martin Lynch,Peter Lynch,,,,,,,,,,,,,
"Thursday, 28 October 2010",Alex King,Eugene Fitzwilliam,Daniel Fogarty,Tanya O'Connell,Steve O'Connell,,,,,Tim Sharpe,Jordan Anthony,Sam Fairbrother,,,,,,,,,,,,,
"Saturday, 30 October 2010",,,,Tanya O'Connell,Steve O'Connell,Jack Krohn,Andrew Krohn,Justin Murray,Eugene Fitzwilliam,,,,,,,,,,,,,,,,

The results I'm looking for should look like for each unique entry in the roster:

Alex King
"Tuesday, 5 October, 2010",Junior Training 4
"Thursday, 7 October, 2010",Warm Up
"Tuesday, 12 October, 2010",Junior Training 1
"Tuesday, 19 October, 2010",Junior Training 3
"Thursday, 28 October, 2010",Warm Up

 

[GlennUK]

Hi dsharpe1977, and welcome to the board.

You will find this so easy once you have your data as a normalized list. Once that is done you can summarise data many ways or simply filter, to have output as you require.

See if you can follow this:
http://www.tek-tips.com/faqs.cfm?fid=5287

By the way, I recommend converting your date strings to actual Excel dates, before you start.