Results 1 to 6 of 6

Thread: Duplicates

  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    London, Gtr London, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicates

    I have a spreadsheet which summarises the number of projects a user can be working on. I have a column with user initials, and each user could have multiple entries. I am looking for a formula which will tell me the total number of different users.

    Any help Please

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates

    Use a pivot table. Drag the header to the Users' column into the DATA area. The result will be the # of users. If you wanted to see the # of projects each user has, add a column of ones to your table(use a header like "counter" or something). Then with the header to the "user initials" column as the column drag the "counter" heading into the DATA area. If you have never used Pivot Tables before, you will first have to go to View, Toolbars, and check PIVOT TABLE.

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    London, Gtr London, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates

    Thanks for that. I will be using a pivot table eventually. Initially I am just trying to find the number of users so that I can use that figure in a formuala

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates

    Derek,
    You could use the subtotal command to achieve this. First sort the user column the Data/Subtotal ... at each change in User - count

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates

    You can use a simple array formula for this task.

    =SUM(1/Countif(user_range,user_range)) Enter the formula via Cntrl-Shift-Enter which will put 'curly braces' around the formula.

    user_range is the range of cells in which your user's intials are.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates

    Block out the column of user names and select DAATA, FILTER, Advanced Filter. Click Unique records, copy to another location and then click in the Location box. At this point, I like to chose the location just below the last row by clicking on it. Then, I say OK and next I go to EDIT, Delete, Shift Cells Up. You will be left with a list of unique users which you can count in any number of creative ways.

Posting Permissions

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