Identify Duplicates

[danpre]

Hi Team,

I Have Data in my excel where i need to findout the Duplicates as well as unique if there are duplicates in the given column.

For eg In Column "M" if there are Five "ABC" so i need all the five ABC as a Duplicates and not only 4 ABC as Duplicates and one as Unique.

ABC
B
C
ABC
ABC
ABC
ABC
D
E

I Hope you understand my requirement and accordingly please provide me the VBA code if you have.

Thanks

 

[adam087]

Try in column B
=IF(COUNTIF(A:A, A1)>1, "This and "&(COUNTIF(A:A, A1)-1)&" other occurances.", "Only occurance")

Regards
Adam

 

[danpre]

This is not working

This code is ignoring the 1st unique record and taking other 4 as Duplicates records and also taking additional record as Duplicate which is not at all duplicate.

Please help

 

[adam087]

In that case I'm probably not understanding what you're looking for; apologies. This is how it works in my sheet:

Excel Workbook
	A	B
1	ABC	This and 4 other occurances.
2	ABC	This and 4 other occurances.
3	B	Only occurance
4	C	Only occurance
5	ABC	This and 4 other occurances.
6	ABC	This and 4 other occurances.
7	A	This and 1 other occurances.
8	A	This and 1 other occurances.
9	F	Only occurance
10	G	This and 1 other occurances.
11	ABC	This and 4 other occurances.
12	X	Only occurance
13	G	This and 1 other occurances.
Sheet1

Excel 2002

Cell Formulas
Range	Formula
B1	=IF(COUNTIF(A:A, A1)>1, "This and "&(COUNTIF(A:A, A1)-1)&" other occurances.", "Only occurance")

Of you could try this?

Excel Workbook
	A	B
1	ABC	First occurance, 4 duplicates later in list.
2	ABC	Duplicate
3	B	Only occurance
4	C	Only occurance
5	ABC	Duplicate
6	ABC	Duplicate
7	A	First occurance, 2 duplicates later in list.
8	A	Duplicate
9	F	Only occurance
10	G	First occurance, 1 duplicates later in list.
11	ABC	Duplicate
12	X	Only occurance
13	G	Duplicate
14	A	Duplicate
Sheet1

Excel 2002

Cell Formulas
Range	Formula
B1	=IF(AND(COUNTIF(A:A, A1)>1, COUNTIF($A$1:A1, A1)>1), "Duplicate", IF(COUNTIF(A:A, A1)>1, "First occurance, "&(COUNTIF(A:A, A1)-1)&" duplicates later in list.", "Only occurance"))

If this doesn't help, give me some more info on what you're looking for.

Regards
Adam

 

[Plastik808]

try this:

Dim toAdd As Boolean, uniqueNumbers As Integer, i As Integer, j As Integer

Cells(1,2).Value = Cells(1,1).Value
uniqueNumbers =1
toAdd = True

For i =2 To 10 (this can be as many as you like)

For j =1 To uniqueNumbers

If Cells(i,1).Value = Cells(j,2).Value Then
toAdd = False
End IF
Next j

If toAdd = Treu Then
Cells(uniqueNumbers + 1,2).Value = Cells(i,1).Value
uniqueNumbers = uniqueNumbers + 1
toAdd = True
Next i

 

[danpre]

Formula is working fine - Thanks

But when i am using the code which you have given - it is giving me overflow error because i have 55,000 row data.

By the way i didnt understand the code little bit.

I mean if i put VBA code in Editor what output it is going to give me in Column B.

Please help me to understand this code

 

[Plastik808]

Column B will be the unique numbers...