+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    03-30-2013
    Location
    Manassas, VA
    MS-Off Ver
    Excel 2012
    Posts
    9

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

    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
    Last edited by dmarzo1; 04-14-2013 at 11:37 AM. Reason: clean up wording

  2. #2
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

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

    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

  3. #3
    Registered User
    Join Date
    03-30-2013
    Location
    Manassas, VA
    MS-Off Ver
    Excel 2012
    Posts
    9

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

    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

  4. #4
    Registered User
    Join Date
    03-30-2013
    Location
    Manassas, VA
    MS-Off Ver
    Excel 2012
    Posts
    9

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

    Quote Originally Posted by dmarzo1 View Post
    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

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,521

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

    This should do what you want
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-30-2013
    Location
    Manassas, VA
    MS-Off Ver
    Excel 2012
    Posts
    9

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

    Quote Originally Posted by jindon View Post
    This should do what you want
    Please Login or Register  to view this content.
    Worked Beautifully!

  7. #7
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1