Duplicate Function

[Manni]

Hi,

I have a data below and I want to creat a function to tell me if the number
I am working on is a duplicate or not. How do I do this?

1
2
3
4
1
4
3

 

[David Biddulph]

=IF(COUNTIF(A$1:A$7,A1)>1,"Duplicate","Notduplicate")

 

[Otto Moehrbach]

What do you mean by "if the number I am working on"? What constitutes
"working on"? Do you mean the number you just entered? If that's the case
then you will need VBA. The following macro will check all the cells in
A1:G20 if a number (or any value) is entered in any cell in A1:G20. Change
the range as needed. You MUST place this macro in the sheet module of your
sheet. To access that module, right-click on the sheet tab and select View
Code. "X" out of the module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:G20")) Is Nothing Then
If Application.CountIf(Range("A1:G20"), Target.Value) > 1 Then
MsgBox "That's a duplicate.", 16, "Duplicate"
End If
End If
End Sub