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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.