CODEWITHSUNDEEP
excelexcel-formulacount
Tuberose
Tuberose

Reputation: 444

Count of duplicated values in a range, and the list of them

For exampe in the range below:

               A

 1          23213400
 2          25470790
 3          25630720
 4          23213400  'Here is a dupplucate happenes, 1
 5          23213400  'Here is a dupplucate happenes, 1
 6          26620401
 7          25470790  'Here is a dupplucate happenes, 2
 8          40029022
 9          22222290
 10         22222290  'Here is a dupplucate happenes, 3

In above, which values are duplucated: 23213400, 25470790, 22222290.

I need returning the count of this values: 3

And the list of this duplicated values:

               B
 1          23213400
 2          25470790
 3          22222290

Upvotes: 0

Views: 64

Answers (1)

barry houdini
barry houdini

Reputation: 46331

Assuming data in A1:A10, with B1 blank or text header use this array formula in B2

=IFERROR(INDEX(A$1:A$10,MATCH(1,(COUNTIF(A$1:A$10,A$2:A$10)>1)*(COUNTIF(B$1:B1,A$1:A$10)=0),0)),"")

confirmed with CTRL+SHIFT+ENTER and copied down column until you start getting blanks

See screenshot below enter image description here

Upvotes: 3

Related Questions