Re--organizing Data from Cell Range: List all occurences under one category label

[dmarzo1]

I will preface my question below by indicating that I think this question has been asked in some form or fashion many times in various forums but I can't seem to find the right answer. I am happy to do with formulas and I am happy to try VBA (of which I have a rudimentary knowledge)

My question is simple enough (I think)

I have data like the following

Abreu, Tony 2B SF Free Agent
Adams, Matt 1B STL Free Agent
Adams, Mike RP PHI Amelie
Affeldt, Jeremy RP SF Free Agent
Alonso, Yonder 1B SD Pourin Raines
Alvarez, Henderson SP MIA GMs
Alvarez, Pedro 3B PIT Guns
Amarista, Alexi 2B SD Free Agent


(It is a list of players with their fantasy team status)

I simply want to extra the data - and have a column for each status

For example:
Free Agent: Abreu, Adams, Affeldt, Amarista (Each listed in separate rows with no zeros or blanks-preferably going vertically)
Amelie: Adams
Pourin Raines: Alonso
Guns: Alvarez


By using pivot tables, I got something that looked like this (using different underlying data from above) ....which is close....but I want the team name to appear only once with all players listed underneath...this will in turn feed a drop down list

Amelie Mujica, Edward RP STL
Amelie Olivo, Miguel C MIA
Amelie Pollock, A.J. CF ARI
Amelie Ramirez, Aramis 3B MIL
Amelie Russell, James RP CHC
Amelie Sandoval, Pablo 3B SF
Amelie Segura, Jean SS MIL
Amelie Street, Huston RP SD
Amelie Uggla, Dan 2B ATL
Amelie Westbrook, Jake SP STL
Amelie Young, Michael 1B PHI
Barnestormers Boggs, Mitchell RP STL
Barnestormers Brothers, Rex RP COL
Barnestormers Fernandez, Jose SP MIA
Barnestormers Francisco, Juan 3B ATL
Barnestormers Gonzalez, Alex SS MIL

I am at my wit's end - this should be relatively easy I would think but not for a simpleton like me! I tried dget function put it gives blanks and I want to eliminate blaks

I appreciate any help

Thanks
Dave