# Counting Unique Values For Duplicate Values

1. ## Counting Unique Values For Duplicate Values

I have a client that wants a report showing how many unique dates of services there are for each one of their customers. Is this possible? I'm thinking some sort of formula that would count as it goes. (Example 1)

Then to add another twist, I'd like to make another list from this one that would show each account number once and the total number of unique date of service instances. (Example 2)

Example 1:

Cust # Date of Service Num Unique
12345 09/01/2009 1
12345 09/01/2009 1
12345 09/01/2009 1
12345 09/15/2009 2
23456 09/20/2009 1
23456 09/25/2009 2
23456 09/25/2009 2

Example 2

Cust # Num Unique
12345 2
23456 2

Thanks, hope this makes sense

2. ## Re: Counting Unique Values For Duplicate Values

With your posted data in cells A1:B8
``Please Login or Register  to view this content.``

and

F1: Cust #
F2: 12345
F3: 23456

G1: Num Unique

This regular formula returns the count of unique dates for each cust #

``Please Login or Register  to view this content.``

Copy that formula down through G3

Using the sample data, these are the results:

``Please Login or Register  to view this content.``
Does that help?

3. ## re: Counting Unique Values For Duplicate Values

Sorry it took me so long to respond. I was out of town.

This formula works but I have over 3400 rows of info in my file and it seems that it won't work with that many rows?

4. ## re: Counting Unique Values For Duplicate Values

OK....If you want formulas to list the unique Cust #'s and the count of unique dates for each, try this:

With
A2:A4000 containing Cust #'s
B2:B4000 containing dates

This ARRAY FORMULA, committed by holding down CTRL and Shift when you press ENTER
(instead of just pressing ENTER) returns the first Cust # value:
``Please Login or Register  to view this content.``
...and this ARRAY FORMULA begins listing subsequent Cust # values:
``Please Login or Register  to view this content.``
Copy F3 and paste the formula into F4 and down as far as you need.

This REGULAR FORMULA returns the count of unique dates for each Cust #:

``Please Login or Register  to view this content.``

Copy that formula down as far as you need.

Does that help?

5. ## Re: Counting Unique Values For Duplicate Values

My Suggestion..

Create Pivot .. it will automatically do it

6. ## Re: Counting Unique Values For Duplicate Values

Originally Posted by Shijesh Kumar
My Suggestion..

Create Pivot .. it will automatically do it
Actually, the pivot table will list each unique Cust #...but the data section
will calculate the count of ALL dates for each of those Cust #'s, not the count
of unique dates.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1