Thanks Thanks:  0
Likes Likes:  0
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 41

Thread: Help with Counting Unique Duplicates

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with Counting Unique Duplicates

    Ok I will keep it simple with two rows. What I am trying to do is count unique duplicate values from one row into another row.

    Date Draw1 Draw2 Draw3 Draw4
    9/16/2014 6 9 7 9
    9/15/2014 6 1 9 3
    I am trying to count the number of repeat digits that occurred in draw 9/15 to 9/16. I am stuck with this formula and it gives me 3 which should be 2. This is my current formula and I am stuck right now. Please note I want to count the unique digits. For example the 6 and 9 was drawn on September 15 but it also came up on 9/16 too but it is counting the 9 twice but it should be once.

    =SUM(IFERROR(SUM(1/COUNTIF(B21:E21,B20)),0)+IFERROR(SUM(1/COUNTIF(B21:E21,C20)),0)+IFERROR(SUM(1/COUNTIF(B21:E21,D20)),0)+IFERROR(SUM(1/COUNTIF(B21:E21,E20)),0)
    )

    Any help would be greatly appreciated.

    Thanks

    Calvin

  2. #2
    Board Regular azumi's Avatar
    Join Date
    Jun 2013
    Location
    Indonesia
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Counting Unique Duplicates

    Try this array formula:

    =(COUNTIF(B2:E3,"<"&B2:E3))

  3. #3
    New Member
    Join Date
    Sep 2014
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Counting Unique Duplicates

    Quote Originally Posted by azumi View Post
    Try this array formula:

    =(COUNTIF(B2:E3,"<"&B2:E3))
    The formula returned "0".

    Not sure what is wrong. Hmmnn

  4. #4
    Board Regular azumi's Avatar
    Join Date
    Jun 2013
    Location
    Indonesia
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Counting Unique Duplicates

    When ENTER you need press CTRL-SHIFT-ENTER button together, do not ENTER alone

    regards

  5. #5
    New Member
    Join Date
    Sep 2014
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Counting Unique Duplicates

    Oh I forgot about that with array formulas. That works.. Thanks a million now let me replicate this throughout the spreadsheet. Saved me some headaches tonight. I really appreciate your help here.

  6. #6
    New Member
    Join Date
    Sep 2014
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Counting Unique Duplicates

    I hate to come back at you one more time but I think something is wrong. I used the formula and it works for two rows but when I added a 3rd row and tried to copy the formula it is giving me a value of 4. Now 9/15 and 9/14 it should be 2 again instead of 4.. Any suggestions? I tried the absolute references even with $b2:$e4 or $b$2:$e$4 but it is not computing right. I removed the references and tried to copy down but it is not calculating right. Once again thanks for your help.

    Date Draw1 Draw2 Draw3 Draw4
    9/16/2014 6 9 7 9
    9/15/2014 6 1 9 3
    9/14/2014 7 4 1 9

  7. #7
    New Member
    Join Date
    Sep 2014
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Counting Unique Duplicates

    Here are the two formulas:

    Cell F2 formula gives me "2"

    =(COUNTIF(B2:E3,"<"&B2:E3))

    But Cell F3 formula gives me "4"

    =(COUNTIF(B3:E4,"<"&B3:E4))

    And of course I hit CTRL-SHIFT-ENTER. Scratching my head here.. Thanks in advance

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,672
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Help with Counting Unique Duplicates

    If there are no blank cells maybe something like this


    A
    B
    C
    D
    E
    F
    G
    1
    Date
    Draw1
    Draw2
    Draw3
    Draw4
    Duplicates
    2
    09/16/2014
    6
    9
    7
    9
    2
    3
    09/15/2014
    6
    1
    9
    3
    2
    4
    09/14/2014
    7
    4
    1
    9


    Formula in G2 copied down
    =SUMPRODUCT(1/COUNTIF($B2:$E3,$B2:$E3),--((1/COUNTIF($B2:$E3,$B2:$E3))<>1))

    Hope this helps

    M.

  9. #9
    New Member
    Join Date
    Sep 2014
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Counting Unique Duplicates

    Thanks That is much cleaner. Do you have any reference docs I can go and read on how to use the "--" characters and "<>1". I am just trying to understand how it calculates. The evaluate function is hard to read on my screen. I only have a 15 inch monitor.

  10. #10
    Board Regular
    Join Date
    Sep 2014
    Location
    mesa, az
    Posts
    221
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Counting Unique Duplicates

    I think this one does what you want also... can enter it in row 3 and fill down.

    {=SUM(IF(COUNTIF(B2:E3,B2:E3)=1,0,1/COUNTIF(B2:E3,B2:E3)))}

    just realized it's pretty much the same that marcelo wrote- just wrote a little differently.

Some videos you may like

User Tag List

Tags for this Thread

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
  •