CODEWITHSUNDEEP
excelvba
Parseltongue
Parseltongue

Reputation: 11657

Count how many values have duplicates in a column

I have values in a column like:

08FHI800
08FHI800
08FHI800
07FJM933
07FJM933
89MNA900

I need a formula that tells me how many items in the column have corresponding duplicates. In this case, it would be 2.

Upvotes: 0

Views: 8225

Answers (2)

activethistle
activethistle

Reputation: 1

=SUMPRODUCT((A1:A12<>"")/COUNTIF(A1:A12,A1:A12&"")-(COUNTIF(A1:A12,A1:A12&"")=1))

Using this data as an example

Row#  ColA

1      1

2      2

3      2

4      2

5      3

6      4

7      5

8      5

9      6

10     6

11     6

12     7

Break the function apart into 3 components:

(A1:A12<>””)

COUNTIF(A1:A12,A1:A12&””)

COUNTIF(A1:A12,A1:A12&””)=1

Component 1

(A1:A12<>””) evaluates to an array containing {T, T, T, T, T, T, T, T, T, T, T, T} ---------(1)

Component 2

COUNTIF(A1:A12,A1:A12&””)

evaluates to 

COUNTIF({1,2,2,2,3,4,5,5,6,6,6,7},{1,2,2,2,3,4,5,5,6,6,6,7}

and it counts the number of times each value appears in the range This in turn evaluates to:

{1,3,3,3,1,1,2,2,3,3,3,1}    -----------------(2)

(the &”” is to avoid #DIV/0 error)

Now, because of the brackets we need to evaluate (Component 1 / Component 2) first before looking at Component 3.

Component 1/Component 2 is

(A1:A12<>"")/COUNTIF(A1:A12,A1:A12&"")

So from (1) and (2),

{T,T,T,T,T,T,T,T,T,T,T,T}/{1,3,3,3,1,1,2,2,3,3,3,1}

which evaluates to:

{1,0.3333,0.3333,0.3333,1,1,0.5,0.5,0.333,0.333,0.333,1}

Now we can look at Component 3

COUNTIF(A1:A12,A1:A12&””)=1

We already have the first bit of this: 

COUNTIF(A1:A12,A1:A12&””)

from (2), which evaluates to

{1,3,3,3,1,1,2,2,3,3,3,1}

Combining this with =1 becomes

COUNTIF(A1:A12,A1:A12&””)=1

Which in turn evaluates to 

{T,F,F,F,T,T,F,F,F,F,F,T}

So, finally combining this all together, we have 

SUMPRODUCT({1,0.3333,0.3333,0.3333,1,1,0.5,0.5,0.333,0.333,0.333,1} - {T,F,F,F,T,T,F,F,F,F,F,T})

Now, T equates to 1 and F equates to 0 so this now becomes:

SUMPRODUCT({1-1,0.333-0, 0.333-0, 0.333-0,1-1,1-1,0.5-0,0.5-0, 0.333-0, 0.333-0, 0.333-0,1-1}

Becomes 

SUMPRODUCT({0,0.333,0.333,0.333,0,0,0.5,0.5,0.333,0.333,0.333,0}

As there is only one array, SUMPRODUCT simply sums the elements

1+1+1 =3

There are 3 items which are duplicated.

Upvotes: 0

barry houdini
barry houdini

Reputation: 46331

Try this formula assuming data in A2:A100

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")-(COUNTIF(A2:A100,A2:A100&"")=1))

It will ignore blanks

Upvotes: 2

Related Questions