DUPLICATES

[ebraun]

I HAVE A SHEET OF 550 LINES WHAT IS THE BEST WAY TO CHECK IF THERE'S NO DUPLICATE

 

[ChrisUK]

You need to sort the range (makes it run faster as you need shorter loops)

Then use VB code to search.

Sub Duplicates()

For x = 1 To 8
For y = x + 1 To 8
If Cells(x, 1).Value = Cells(y, 1).Value Then
rtnVal = MsgBox("Duplicate at row " & Str, vbCritical + vbOKOnly, "Error!!")
End If
Next y
Next x

End Sub

HTH

 

[WJReid]

Hi ebraun,

If you select the range A1:A500 and then go to Format|Conditional Formatting and in the formula box type:

=($A$1:$A$500,a1)>1

then select your format e.g. red pattern. Any duplicates will now be highlighted with a red background.

Regards,

Bill

 

[WJReid]

Hi ebraun,

If you select the range A1:A500 and then go to Format|Conditional Formatting and in the formula box type:

=($A$1:$A$500,a1)>1

then select your format e.g. red pattern. Any duplicates will now be highlighted with a red background.

Regards,

Bill

 

[WJReid]

ADVERTISEMENT

Hi ebraun,

If you select the range A1:A500 and then go to Format|Conditional Formatting and in the formula box type:

=($A$1:$A$500,a1)>1

then select your format e.g. red pattern. Any duplicates will now be highlighted with a red background.

Regards,

Bill

 

[ebraun]

BUT I AM GETTING AN ERROR MASSAGE SAYING

YOU MAY NOT USE UNIONS, OR ARRAY CONSTANTS FOR CONDITIONAL FORMATTING CRITERIA

CAN ANY ONE HELP

 

[ebraun]

ADVERTISEMENT

BUT I AM GETTING AN ERROR MASSAGE SAYING

YOU MAY NOT USE UNIONS, OR ARRAY CONSTANTS FOR CONDITIONAL FORMATTING CRITERIA

CAN ANY ONE HELP

 

[Jim North]

Bill was on the right track, use:

=COUNTIF($A$1:$A$500,A1)>1

 

[ebraun]

BUT IT SAYS TRUE OR FALSE I WANT TO SEE TOTAL # OF DUPLICATES AND WHAT THE DUP ARE NO VBA PLEASE

 

[Juan Pablo González]

No need to yell around here.

=COUNTA(F1:F23)-SUM(1/COUNTIF(F1:F23,F1:F23))

will return the number of duplicate items in F1:F23. That is an array formula. To return a list of unique items (Wich makes more sense than a list of duplicates) use Advanced Filter.