Possible Duplicate Warning issue

[mykal66]

Hi everyone.

I'm stuck trying to get a possible duplicate warning to work properly. Any help or pointers would be very much appreciated as always.

In short i need a message box to advise the user that there is a possible duplicate. I found some code elsewhere (attached example workbook) which works great except i only need it to compare date entered into the last cell aginst the rest of the column. At the moment is warns whatever i enter or even click on an empty cell.

Thank you

Mykal12763

[p45cal]

Data validation. In the attached data validation in cells A1:A20 custom formula:
=COUNTIF($A$1:$A$20,A1)=1
(entered into the whole range in one go but with A1 the active cell)

[mykal66]

Hi and thanks for your help. Unfortunately this doesn't quite work as i need it to because the the range will not necessarily be unique so i need a warning to advise it is a possible duplicate, but then allow the user to enter it anway if necessary.

The column is used for name e.g. Adam Smith and there could genuinely be more than one Adam Smith, in my example the code just warns the user that it could be a duplicate and gives them the row number so they can check other details and either delete the entry if it is a duplicate or carry on if it is a different adam smith.

In the code in my example i just need it to only check if the latest data entered may be a duplicate rather than check the whole column every time. In the example if i enter a new same 'Sarah' this would not be a duplicate but it still reports the duplicate 'Mykal' entries which may have already been accepted as valid.

Thank you again for you help - I can actually use your method on another sheet i am working on where there is a unique reference number which should never be duplicated.

[p45cal]

so i need a warning to advise it is a possible duplicate, but then allow the user to enter it anway if necessary.On the 3rd tab of the Data Validation dialogue box (Error Alert) there is a dropdown (Style) where the second option is Warning. That should give you what you want.

[mykal66]

Hi again and thank you to p45cal for the above method for checking duplicates which i have now used in my worksheet.

I would still like to use the vba method in my example if anyone can help because as well as checking it also advises the user which row the possible duplicate is on. At the moment it checks and reports for every entry in the row, even if a possible duplicate has already been checked and given the OK. I only need it to look at the latest entry into the row and complare everything above for a possible duplicate

Many thanks - Mykal

[p45cal]

try this code instead of your existing code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Variant, s As String
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & i).Value = Target.Value And Target.Row <> i Then s = s & vbNewLine & Range("A" & i).Value & " " & i
Next i
If s <> "" Then MsgBox "Duplicate Names In Rows:" & vbNewLine & s, 48, "Duplicate Names"
End Sub

[mykal66]

Hi again p45cal.

Brilliant. Thanks you so much. we have just set up the sheet so your initial method is great (and i learned something new from it too) but eventulaly there will be a lot of data so reporting the line number the duplicate/s were on was better.

Thank you so much for your time and patience.

As always with you guys on here, i try not to ask for help very often but when i do there is always someone who is happy to help and share knowledge and it is very mcuh appreciated.

best wishes

Mykal