Forum Discussion

Anıl Adaş's avatar
Anıl Adaş
Brass Contributor
Apr 15, 2019
Solved

Remove Dublicates and Count (help pls)

I have a column and I simply need to count them with the help of an excel formula.   I add an example excel file to explain case easy. There are some numbers which I cannot remove but also cannot c...
admin
excel
Formulas and Functions
Macros and VBA
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 15, 2019

    Anıl Adaş , if calculate how many values have duplicates, that's

    =SUMPRODUCT((A2:INDEX(A:A,COUNTA(A:A))<>"")/COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A))&"")-
                (COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A))&"")=1))

    That means if, for example, value ABC is repeated 4 times and other have no duplicates, formula returns 1 since that's only one value has duplicates.

     

    In your case it could be simplified to

    =SUMPRODUCT(1/COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A)))-(COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A)))=1))

    assuming you have no blank cells within the range.

     

    If you have ABC repeated 4 times and one XYZ, when

    total = 5

    with duplicates = 1

    count ignoring duplicates = 2

Anıl Adaş's avatar
Anıl Adaş
Brass Contributor
Apr 15, 2019

SergeiBaklan Hi, thanks your response first. Your formula is very helpful but I must change A2:A46 in formula every time. If I have more number in column A, I have to change formula like A2:A900;A2:A900 for 900 number. I need a constant formula for every time usage.

 

If someone have an idea for this pls let me know :)

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 15, 2019

    Anıl Adaş , you may use dynamic range like

    =SUMPRODUCT(1/COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A))))

    assuming you have no other data below in your column. Formula is in F2 attached.

     

    • Anıl Adaş's avatar
      Anıl Adaş
      Brass Contributor
      Apr 15, 2019

      SergeiBaklan Thanks a lot for your formula its very useful. I have one more question about this case. Do you know a way to find how much dublicate I have in column A as I marked green at attached example.

      • Anıl Adaş's avatar
        Anıl Adaş
        Brass Contributor
        Apr 15, 2019

        Actually I can easily find the number of dublicates like "total number of Column A - total number without dublicates" but is it possible to calculate doing opposite like "total number of Column A - dublicates"

         

        Its hard to explain why but actually I exactly need to find a formula for D7 on yellow marked line on this example file.

Resources