Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Counting Rows with Duplicate Data

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't know if its possible to do this in Excel. My worksheets has clients with duplicate rows. I'm attempting to tally the number of clients without counting them more than once.

    i.e.:

    client a
    client a
    client a
    client b
    client b
    client c

    total client: 3 instead of 6

    Thanks in advance for your help.
    Rob

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-23 12:39, ralamo wrote:
    I don't know if its possible to do this in Excel. My worksheets has clients with duplicate rows. I'm attempting to tally the number of clients without counting them more than once.

    i.e.:

    client a
    client a
    client a
    client b
    client b
    client c

    total client: 3 instead of 6

    Thanks in advance for your help.
    Rob
    Either

    =SUM(IF(FREQUENCY(MATCH(A2:A100,A2:A100,0),MATCH(A2:A100,A2:A100,0)),1))

    or, array-enter,

    =SUM(IF(LEN(A2:A100),1/COUNTIF(A2:A100,A2:A100)))

    Note. To array-enter a formula hit control+shift+enter at the same time, not just enter.





    [ This Message was edited by: Aladin Akyurek on 2002-04-23 12:52 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-23 12:39, ralamo wrote:
    I don't know if its possible to do this in Excel
    Never say that! Anything is possible in Excel. I knew Aladin would get to you soon enough.

    [ This Message was edited by: John McGraw on 2002-04-23 12:57 ]

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you very, very much, Aladin. The first formula worked. I will never doubt Excel ever again, John.

    I'm a true believer, now.


  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Otherwise try the advanced data filter-> unique record only. Then marking duplcate rows and couting them.

    Even better: use SAS to tacle these kind of things.
    NOx installed

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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