I found many posts that have solution to prevent entering duplicate values in a column's cell.
I am trying to solve the same problem which has little different flavor.
In above table I want to stop users if they try to enter the same value (before "-").
E.G. A1-a if already there, A1 should not be allowed.
Essentially, the logic should first...
run this formula "=LEFT(C1,FIND("-",C1)-1)"
then run the list validation using countif.
I am using the folloiwng line of vb code but don't know how do i add the LEFT formula component to it or update the code to prevent duplicate comparing string before "-" i.e. dash.
Private Sub Worksheet_Change(ByVal Target As Range) If Application.CountIf(Range("a:a"), Target) > 1 Then MsgBox "Duplicate....", vbCritical, "Can't take dups :(" 'what this should do after the error msg is closed Target.Value = "" End If End Sub