+ Reply to Thread
Results 1 to 10 of 10

Count duplicate records

  1. #1
    Registered User
    Join Date
    12-06-2009
    Location
    Asia Pacific
    MS-Off Ver
    Excel 2003
    Posts
    30

    Lightbulb Count duplicate records

    I want to be able to count the total number of unique records for my analysis.

    However.... I cannot do this by simply highlighting the column and seeing what the cell count is because there are duplicates in my data.

    column A has a list of id's eg below: There are 5 unique records not 10.

    I want to be able to get this unique record count for my long list of data.

    123
    123
    123
    456
    456
    750
    100
    236
    236
    236

    Is there a simple formula I can use for this???

    Many thanks in advance.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Formula to count dupplicate number of records in a cloumn

    Hi stats,

    depending on the data type:

    if numeric

    =SUM(IF(FREQUENCY(A1:A10, A1:A10)>0,1))

    confirm with Ctrl-Shift-Enter

    if mixed text and numbers

    =SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""), IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>0,1))

    again, confirm with Ctrl-Shift-Enter

    Adjust ranges to suit.

  3. #3
    Registered User
    Join Date
    12-06-2009
    Location
    Asia Pacific
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Formula to count dupplicate number of records in a cloumn

    Yes exactly 1 instance of each entry.

    Thanks so much....I will try that now and let you know if it worked. Cheers


    I tried this and I thought I resolved it but no.....
    =COUNTA($A:$A)-1

  4. #4
    Registered User
    Join Date
    12-06-2009
    Location
    Asia Pacific
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Count duplicate records

    Thanks so much that first formula you provided worked.....but the ctr-shift-enter to confirm.......not so sure what was supposed to happen there????.......is it supposed to verify that my answer is correct?

    =SUM(IF(FREQUENCY(A1:A10, A1:A10)>0,1))

  5. #5
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Count duplicate records

    No, it's not about verification. The formula is an array formula. It evaluates not only individual cells, but arrays consisting of many cells. Array formulae must be confirmed with Ctrl-Shift-Enter. This will put curly brackets around the formula in the formula bar, so you can distinguish an array formula from a normal one. As soon as you edit the formula, the brackets no longer display and you must remember to hit Ctrl-Shift-Enter to save your formula changes.

  6. #6
    Registered User
    Join Date
    12-06-2009
    Location
    Asia Pacific
    MS-Off Ver
    Excel 2003
    Posts
    30

    Talking Re: Count duplicate records

    Quote Originally Posted by teylyn View Post
    No, it's not about verification. The formula is an array formula. It evaluates not only individual cells, but arrays consisting of many cells. Array formulae must be confirmed with Ctrl-Shift-Enter. This will put curly brackets around the formula in the formula bar, so you can distinguish an array formula from a normal one. As soon as you edit the formula, the brackets no longer display and you must remember to hit Ctrl-Shift-Enter to save your formula changes.
    Teylyn......

    Thanks for that clarification..........Thanks again for your help..........this formula is invaluable...........I will find alot of use for this in my day to day work.

    Cheers

  7. #7
    Registered User
    Join Date
    12-06-2009
    Location
    Asia Pacific
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Count duplicate records

    Teylyn,

    One more question this formula =SUM(IF(FREQUENCY(A1:A10, A1:A10)>0,1)) does not seem to work in excel 2007....?

    Thanks

  8. #8
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Count duplicate records

    did you hit Ctrl-Shift-Enter or just Enter?

  9. #9
    Registered User
    Join Date
    12-06-2009
    Location
    Asia Pacific
    MS-Off Ver
    Excel 2003
    Posts
    30

    Talking Re: Count duplicate records

    Quote Originally Posted by teylyn View Post
    did you hit Ctrl-Shift-Enter or just Enter?
    Yep Teylyn,

    That was it.......... I must have hit one wrong key when I did ctr-shft-ent....


    Thanks a million....

    GREAT HELP!!!!.................GREAT SITE!!!!!...............AWSOME FORMULA!!!

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count duplicate records

    If you prefer you can use SUMPRODUCT which (though not really any more efficient than the Array) does not require CTRL + SHIFT + ENTER entry - ie can be confirmed with Enter as normal.

    =SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
    confirmed with Enter

    If using Arrays or SUMPRODUCT it is imperative you keep your ranges "lean".

+ 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