+ Reply to Thread
Results 1 to 6 of 6

Counting Unique Values For Duplicate Values

  1. #1
    Registered User
    Join Date
    01-17-2008
    Posts
    5

    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
    Last edited by Tom_LR; 09-21-2009 at 04:21 PM. Reason: Looking for specific member

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    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?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    01-17-2008
    Posts
    5

    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?
    Last edited by DonkeyOte; 09-21-2009 at 06:11 PM. Reason: Unnecessary Quote Removed

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    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. #5
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717

    Re: Counting Unique Values For Duplicate Values

    My Suggestion..

    Create Pivot .. it will automatically do it

  6. #6
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Counting Unique Values For Duplicate Values

    Quote Originally Posted by Shijesh Kumar View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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