Results 1 to 2 of 2

Thread: list (97)

  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Thanked 0 Times in 0 Posts

    list (97)

    A1 contains "Team", B1 contains "Player
    B1:B999 contains unique player names
    A1:A999 contains the teams they play for.

    I want to be able to create a list containing each team just once, and next to it any one player that plays for that team.
    Can I do it with a pivot table?


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: list (97)

    No, a pivot table in Excel can only display numeric aggregates in the value area, not text values.

    You can do this:
    - Copy A1:B1 to D1:E1.
    - Click in A1.
    - Select Data | Filter | Advanced Filter...
    - Click 'Copy to another location' and 'Unique records only'.
    - The 'List range' will be selected automatically.
    - Leave the 'Criteria range' empty.
    - Enter D1 in the 'Copy to' range.
    - Click OK.
    - This will create a list of unique team names in D2 and below.
    - In E2, enter the formula


    - Make sure that E2 is selected.
    - Fill down by double clicking the fill handle (the little black square in the lower right corner of E2).

Posting Permissions

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