Counting only unique records (Excel XP)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 MarkD‘s CountUniqueRecords function – see post 205252.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 MarkD‘s CountUniqueRecords function – see post 205252.

Reply | Quote

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

Reply | Quote

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:

=A2&B2

Copy it to C3:C100

In F1 enter the array formula (confirm with ctrl-shift-enter):

=SUM(IF($B$2:$B$100=E1,1/COUNTIF($C$2:$C$100,$A$2:$A$100&$B$2:$B$100)))

Copy this to F2: Fwhatever

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Steve

Reply | Quote

Thanks, Steve! I appreciate your help

Christa

Reply | Quote

Thanks, Steve! I appreciate your help

Christa

Reply | Quote

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

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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:

=A2&B2

Copy it to C3:C100

In F1 enter the array formula (confirm with ctrl-shift-enter):

=SUM(IF($B$2:$B$100=E1,1/COUNTIF($C$2:$C$100,$A$2:$A$100&$B$2:$B$100)))

Copy this to F2: Fwhatever

Steve

Reply | Quote