Duplicates

[proficient]

I want to find duplicates numbers in a range

Excel Workbook
	A	B	C	D
1	1		1	4
2	2		2	6
3	3		4	3
4	4
5	6
6	1
7	2
8	2
9	4
10	65
11	1
12	2
13	2
14	4
15	25
16	1
17	2
Duplicates Value

 

[norulen]

I think you are looking for only duplicates number in one column eg. 1,2,4 etc. right?? or do you need the count also??

 

[norulen]

This might not be the cleanliest code but try:

Sub Duplicatevalues()Dim lr As Long, r As Long
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To lr
      
    If Application.WorksheetFunction.CountIf(Range("A:A"), Range("A" & r)) > 1 Then Range("A" & r).Copy Destination:=Range("C" & ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row + 1)
Next r
ActiveSheet.Range("C:C").RemoveDuplicates Columns:=1, Header:=xlNo

End Sub

 

[proficient]

I think you are looking for only duplicates number in one column eg. 1,2,4 etc. right?? or do you need the count also??

Yes! I am looking for only duplicate numbers...

 

[norulen]

Did the macro work for you??

 

[proficient]

No...

 

[norulen]

It seems to working totally fine for the sample you gave. I rechecked. what is the error that you are getting?

 

[norulen]

Try this: I have made some small changes. This uses Helper column as AA which will automatically get deleted in the end. But make sure you do not have any data there. If you do change the coloumn reference

Sub Duplicatevalues()
Dim lr As Long, r As Long
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To lr
      Range("AA" & r).Value = Application.WorksheetFunction.CountIf(Range("A:A"), Range("A" & r))
    If Range("AA" & r).Value > 1 Then Range("A" & r).Copy Destination:=Range("C" & ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row + 1)
    
Next r
ActiveSheet.Range("C:C").RemoveDuplicates Columns:=1, Header:=xlNo
Columns("AA:AA").ClearContents
End Sub

 

[FormR]

I want to find duplicates numbers in a range



Duplicates Value

	A	B	C	D
1	1		1	4
2	2		2	6
3	3		4	3
4	4
5	6
6	1
7	2
8	2
9	4
10	65
11	1
12	2
13	2
14	4
15	25
16	1
17	2

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D1 =COUNTIF($A$1:$A$17,C1) D2 =COUNTIF($A$1:$A$17,C2) D3 =COUNTIF($A$1:$A$17,C3) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

This seems to be what you are looking for:
Extract a list of duplicates from a column using array formula in excel | Get Digital Help - Microsoft Excel resource

 

[Marcelo Branco]

A possible solution using formulas

A B C D E

Numbers		DuplicatesList	Count	Duplicates
1		1	4	3
2		2	6
3		4	3
4
6
1
2
2
4
65
1
2
2
4
25
1
2

<TBODY>
</TBODY>

Formula in E2 to get the count of duplicates
=SUMPRODUCT(--(COUNTIF($A$2:$A$18,$A$2:$A$18)>1),--(MATCH($A$2:$A$18,$A$2:A18,0)=ROW($A$2:$A$18)-ROW($A$2)+1))

Array formula in C2
=IF($E$2>=ROWS($C$2:C2),INDEX(A:A,MIN(IF(COUNTIF($A$2:$A$18,$A$2:$A$18)>1,IF(ISNA(MATCH($A$2:$A$18,$C$1:C1,0)),ROW($A$2:$A$18))))),"")

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

Formula in D2
=IF(C2<>"",COUNTIF(A:A,C2),"")

copy down

M.

 

[mirabeau]

Proficient,

Try this code

Sub dups()
Dim c
With CreateObject("scripting.dictionary")
For Each c In Range("A1", Cells(Rows.Count, "a").End(3)).Value
    .Item(c) = .Item(c) + 1
Next
For Each c In .keys
    If .Item(c) = 1 Then .Remove c
Next
Range("C1").Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
End With
End Sub