PDA

View Full Version : [SOLVED] vba or formula to find number of duplicates in a range



aravindhan_3
05-18-2016, 11:28 PM
Hi,

Need your help again!

I have employee ids entered in column A from Row 3 to Row 100000 (Row 2 is a Header), there may be duplicates in that column.

I want use a excel formula to find out how many sets of duplicates are there in cell A1.

Eg:if employee number 123 is repeated 3 times & employee number 345 is repeated 2 times, then I need answers as 2 in A1.

Thanks for your help

Regards
Arvind

mancubus
05-19-2016, 08:25 AM
i am not aware of a solution with a single formula

but this can be done using a helper column.

pick a column outside of the table.

Let this column be N.

array (CSE) formula in N3

=IFERROR(INDEX($A$3:$A$100000,MATCH(0,IF(ISBLANK($A$3:$A$100000),1,COUNTIF( $N$2:$N2,$A$3:$A$100000)),0)),"")
confirm with Ctrl+Shift+Enter
copy down as many rows as needed

formula in O3

=IF(LEN(N3)>0,COUNTIF($A$3:$A$100000,N3),"")
confirm with Enter
copy down as many rows as needed

formula in A1

=COUNTIF($O$3:$O$100000,">1")
confirm with Enter


see the attachment

p45cal
05-19-2016, 10:55 AM
Test in A1:
=SUM(--(FREQUENCY(A3:A100000,A3:A100000)>1))
(works only with numbers)

p45cal
05-19-2016, 12:01 PM
…however,
=ROUND(SUM(1/IF(COUNTIF(A3:A100000,A3:A100000)=0,9E+99,COUNTIF(A3:A100000,A3:A100000)))-SUM(--(COUNTIF(A3:A100000,A3:A100000)=1)),0)

entered with Crtl+Shift+Enter (not just Enter) seems to cater for numbers and text and ignores blanks.

aravindhan_3
05-23-2016, 02:30 AM
Worked, thanks P45Cal & Mancub

Aussiebear
05-23-2016, 03:16 AM
Worked, thanks Mancub
Lets see... called by the wrong name and can't be bothered to mark the thread as solved..... Can't say I'm all that impressed.