Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting only unique records (Excel XP)

    I have an Excel database which is a query of a recruiting activity of our members. I would like to summarize the members in the database by what where they are at in the recruitment process. For instance a member could be categorized as "Follow up required", "Accepted", "Enrolled", "Not interested", "Not Qualified" etc. I would like to count the members in each of the above categories. My problem is that the database records each time a follow up call is made therefore, a member may have 5 records in the database that say "Follow up required" I, however, only want my summary to count the member 1 time (not 5 times).

    Any help would be muchly appreciated. Thanks in advance.

    Christa

  2. #2
    New Lounger
    Join Date
    Nov 2002
    Location
    Miami, Florida, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting only unique records (Excel XP)

    Someone probably has something more elegant but...

    You could use an Advanced filter (filter the Names column and check the Unique Records Only checkbox.) to get a unique list.

    Then, if you only wanted to count the total number of unique names, you would be able to use the SUBTOTAL function to get the count...
    But since you probably need to use COUNTIF, I think you would have to copy the filtered data and paste it onto another sheet. Then you could manipulate it as needed.

    Chris

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting only unique records (Excel XP)

    Thanks that would work...but I'm creating this report for a user who only wants to have to refresh the query to pull the updated records from the database...does you know if what I want to do can be done using a macro or Access?

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting only unique records (Excel XP)

    In Access it would be relatively easy. You can create a query that returns unique combinations of customer and category, then a totals query based on the first one that counts the number of customers per category. Another way is to use <!profile=MarkD>MarkD<!/profile>'s CountUniqueRecords function - see <post#=205252>post 205252</post#>.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting only unique records (Excel XP)

    Here is a formulaic procedure. [Change my imaginings as desired] I imagined that you had 2 columns:
    Members in A2:A100
    Categories in B2:B100

    E1- Ewhatever has the category names ("Follow up required", "Accepted", "Enrolled", "Not interested", "Not Qualified" etc)

    In C2 add the formula:
    <pre>=A2&B2</pre>

    Copy it to C3:C100

    In F1 enter the array formula (confirm with ctrl-shift-enter):
    <pre>=SUM(IF($B$2:$B$100=E1,1/COUNTIF($C$2:$C$100,$A$2:$A$100&$B$2:$B$100)))</pre>


    Copy this to F2: Fwhatever

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting only unique records (Excel XP)

    Thanks, Steve...this get's me 1/2 way there (unless I'm doing something wrong in your example). Because we have more than one record saying "Follow-up Required" for some members...the formula still counts these members more than once

  7. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting only unique records (Excel XP)

    Thanks for the excellent advice, Hans. I'm quite a newby with Access so I'm still trying to see if I can get it to work.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting only unique records (Excel XP)

    The first query would look like this (this is just dummy SQL, you'd need to substitute the actual names):

    SELECT DISTINCT [Member], [Category] FROM [tblSomething]

    Say this query is saved as qryDistinct. The second query would look like this:

    SELECT [Category], Count(*) As [NumberOfMembers] FROM [qryDistinct] GROUP BY [Category]

    If you would like more assistance, you can post a question in the Access forum.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting only unique records (Excel XP)

    Did you confirm with ctrl-shift-enter (if you look at the formula bar there should be "squiggly brackets" ({}) around the formula, put in by excel).

    Could you upload a small example file with your setup and an example? My demo seemed to work fine (so I might not be understanding something)

    Steve

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting only unique records (Excel XP)

    Hi Steve,

    Here is a small sample of my database. I need to be able to count the various reponses in the UF_2 column but only count them once for each unique ID in the ID column. For instance the first ID has 4 "Follow-up Required"...I need this ID to be counted only once in the Follow-up Required category.

    Thanks,

    Christa

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting only unique records (Excel XP)

    If you follow my instructions from <post#=374948>post 374948</post#> (modifying for your actual columns) you will get the attached file.

    Steve

  12. #12
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting only unique records (Excel XP)

    Thanks, Steve! I appreciate your help

    Christa

Posting Permissions

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