Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Actually, I think the pivot table is fine.. the problem lies with me.

    I use a spreadsheet to manage staffing at events. I have various staff at an event and create a row for each person that has the event number and city and so on (see attached). I wanted to use a pivot table to see how many events were performed per city but what I get is the count of staff used at each city.

    In the attached worksheet, I have some sample data - the answer is one event per city but as you can see, I don't get that. I want to continue managing the staff this way (until I find something better) but need to be able to look at the data as I need.

    i would appreciate any help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Pivot tables don't count unique values, they just count rows.

    You can get your count of events by using Advanced Filter twice. To get the attached version I did the following:
    - Copy the column headings City and Event # to I1:J1 and to L1:M1.
    - Click in the data table, and select Data | Filter | Advanced Filter...
    - Select the option to copy records to a different location, specify I1:J1 as destination and tick the check box "Unique records only".
    - Click OK. This produces the table in columns I and J, with unique City/Event # combinations. (I modified the data slightly to better see the effect.)
    - Click in the data table again, and select Data | Filter | Advanced Filter...
    - Select the option to copy records to a different location, specify only L1 (not L1:M1) as destination, and tick the check box "Unique records only".
    - Click OK. This produces the list of unique cities in column L.
    - In M2, enter the formula =COUNTIF($I$2:$I$11,L2) and fill down.

    [attachment=87361:events_modified.xls]
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Given the data, Han's solution is quite corrrect.

    However, if you were to add a new Col of Data, if practiable, you could use a Pivot Table.

    See the attached file. To ensure it worked correctly I added data so that there were mutilple events in the same city.

    Hope this helps.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Tom, your attachment is missing...

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [attachment=87382:Pivot Problem Alt Solution.xls]having trouble with my Browser and the Lounge.

    I Hope it works this time.

    Tom Duthie
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by duthiet View Post
    I Hope it works this time.
    Yes, the attachment is present.

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you both for the responses - two good solutions.

Posting Permissions

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