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

[MickG]

I assumed you data (1st list) is in 5 columns, the last being "E".
The code will produce a list, similar to your 2nd list, starting in "G1".
I'm not really sure where the third list, comes in !!!

Please Login or Register  to view this content.

Regards Mick

[dmarzo1]

excel forum sort example.xlsx

Mick and Others

I have attached a sample spreadsheet - this is just a representative example.
The source data will be rows of data
ANd I simply want to have each occurrence listed under the team name (See cells to the right)

Mick - will your sub work on this? You had set it up as five columns of data - but the player names are actually in one column - does that make a difference?

I appreciate your thoughts

Dave

[dmarzo1]

Attachment 228024

Mick and Others

I have attached a sample spreadsheet - this is just a representative example.
The source data will be rows of data
ANd I simply want to have each occurrence listed under the team name (See cells to the right)

Mick - will your sub work on this? You had set it up as five columns of data - but the player names are actually in one column - does that make a difference?

I appreciate your thoughts

Dave

Mick

I got your code to work - many thanks - just needed to change the offsets since you had assumed five columns and it was only two

Reall appreciate it

Dave

[jindon]

This should do what you want

Please Login or Register  to view this content.

[dmarzo1]

This should do what you want

Please Login or Register  to view this content.

Worked Beautifully!

[MickG]

Athough Jindon's solved you problem.
I had not forgotton you & this is my Modified code:-

Results start "D1"

Please Login or Register  to view this content.

Regards Mick