Prevent duplicate IF left string is repeating

[NAP2012]

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]C1-a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C1-b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]prevent[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C2-a[/TD]
[TD]prevent[/TD]
[/TR]
</tbody>[/TABLE]









Hi,

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

 

[NAP2012]

Hi Robert, this seems very near...except its allowing the same C18 in this example if I enter with dash.

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Record #[/TD]
[TD="class: xl63, width: 64"]ID[/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl65"]C10-a[/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl65"]C10-b[/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl65"]C10-c[/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl65"]C10-d[/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl65"]C11[/TD]
[/TR]
[TR]
[TD="class: xl64"]6[/TD]
[TD="class: xl65"]C12[/TD]
[/TR]
[TR]
[TD="class: xl64"]7[/TD]
[TD="class: xl65"]C13[/TD]
[/TR]
[TR]
[TD="class: xl64"]8[/TD]
[TD="class: xl65"]C14[/TD]
[/TR]
[TR]
[TD="class: xl64"]9[/TD]
[TD="class: xl65"]C15[/TD]
[/TR]
[TR]
[TD="class: xl64"]10[/TD]
[TD="class: xl65"]C16[/TD]
[/TR]
[TR]
[TD="class: xl64"]11[/TD]
[TD="class: xl65"]C17[/TD]
[/TR]
[TR]
[TD="class: xl64"]12[/TD]
[TD="class: xl65"]C18[/TD]
[/TR]
[TR]
[TD="class: xl64"]13[/TD]
[TD="class: xl66"]C18-a
[/TD]
[/TR]
</tbody>[/TABLE]

row number 13 should have not been allowed as C18 exist before, so user need to start with next number or the one which does not exist without dash.
user can enter enter the same number again only if the same number exist with dash like C10-d, so I can enter C10-e.

see the further explanation i posted in the thread above.

 

[Trebor76]

Maybe this:

Option Explicit
Option Compare Text 'Compare text but ignore case sensitivity
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strMyKey As String
    Dim lngLastRow As Long
    Dim rngMyCell As Range
            
    If Target.Column = 1 And Len(Target.Value) > 0 Then
        If InStr(Target.Value, "-") > 0 Then
            strMyKey = Evaluate("LEFT(""" & Target.Value & """,FIND(""-"",""" & Target.Value & """)-1)")
        Else
            strMyKey = Target.Value
        End If
        lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
        For Each rngMyCell In Range("A1:A" & lngLastRow)
            If rngMyCell.Address <> Target.Address Then
                If Left(rngMyCell, Len(strMyKey)) = strMyKey Then
                    MsgBox Target.Value & " has already been entered." & vbNewLine & "As such cell " & Target.Address(False, False) & " will be cleared.", vbCritical
                    Application.EnableEvents = False
                        Target.ClearContents
                    Application.EnableEvents = True
                    Exit For
                End If
            End If
        Next rngMyCell
    End If

End Sub

 

[NAP2012]

[TABLE="class: cms_table, width: 128"]
<tbody>[TR]
[TD="class: cms_table_xl65"]C10-e[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

If I enter C10-e its not allowing that.

 

[Trebor76]

In post 11 you said not to allow C18-a so why is C10-e ok?

 

[Trebor76]

Let me know how this goes and if it's wrong why:

Option Explicit
Option Compare Text 'Compare text but ignore case sensitivity
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strMyKey As String
    Dim lngLastRow As Long
    Dim rngMyCell As Range
    Dim dblMyCount As Double
            
    If Target.Column = 1 And Len(Target.Value) > 0 Then
        lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
        If InStr(Target.Value, "-") > 0 Then
            dblMyCount = WorksheetFunction.CountIf(Range("A1:A" & lngLastRow), Target.Value)
            If dblMyCount = 1 Then
                Exit Sub
            ElseIf dblMyCount > 1 Then
                MsgBox Target.Value & " has already been entered." & vbNewLine & "As such cell " & Target.Address(False, False) & " will be cleared.", vbCritical
                Application.EnableEvents = False
                    Target.ClearContents
                Application.EnableEvents = True
                Exit Sub
            End If
        End If
        'If we get here the ID has been entered without a dash so check all entries against it to ensure it hasn't been used
        For Each rngMyCell In Range("A1:A" & lngLastRow)
            If rngMyCell.Address <> Target.Address Then
                If Left(rngMyCell, Len(Target.Value)) = Target.Value Then
                    MsgBox "To use """ & Target.Value & """ you will need to add a dash to it as well as the next letter for that range." & vbNewLine & "Cell " & Target.Address(False, False) & " will be cleared.", vbCritical
                    Application.EnableEvents = False
                        Target.ClearContents
                    Application.EnableEvents = True
                    Exit For
                End If
            End If
        Next rngMyCell
    End If

End Sub

 

[NAP2012]

In post 11 you said not to allow C18-a so why is C10-e ok?

because C18 without dash exist. While C10 without dash does not exist.

your code is working all fine except its allowing dash entry for the same (C18 in example) number.

[TABLE="width: 206"]
<tbody>[TR]
[TD]Record #[/TD]
[TD]Req.[/TD]
[/TR]
[TR]
[TD]C10-a[/TD]
[TD]exist[/TD]
[/TR]
[TR]
[TD]C10-b[/TD]
[TD]exist[/TD]
[/TR]
[TR]
[TD]C10-c[/TD]
[TD]exist[/TD]
[/TR]
[TR]
[TD]C10-d[/TD]
[TD]exist[/TD]
[/TR]
[TR]
[TD]C10[/TD]
[TD]should not allowed[/TD]
[/TR]
[TR]
[TD]C18[/TD]
[TD]exist[/TD]
[/TR]
[TR]
[TD]c18-a[/TD]
[TD]should not allowed[/TD]
[/TR]
</tbody>[/TABLE]


To satisfy the requirement if I can rephrase to simplify...

• If I am entering number with dash, (e.g. C18-a)
  • the same number without dash should not exist before (C18)
  • and exact same number with dash should also not exist before (C18-a)

• If I am entering number without dash, (e.g. C10)
  • same number with dash should not exist before (C10-a/b/c...)
  • the exact same number (without dash) should not exist before (C10)

 

[Trebor76]

OK - last attempt:

Option Explicit
Option Compare Text 'Compare text but ignore case sensitivity
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strMyKey As String
    Dim lngLastRow As Long
    Dim rngMyCell As Range
    Dim dblMyCount As Double
    Dim rngMyRange As Range
    
    'Code built to only work on a single cell in Col. A
    'Adapted from here https://www.mrexcel.com/forum/excel-questions/651154-vba-worksheet_change-event-how-detect-if-multiple-cells-were-selected.html
    Set rngMyRange = Intersect(Target, Range("A:A"))
    If Not rngMyRange Is Nothing Then
        If rngMyRange.Cells.Count > 1 Then
            Exit Sub
        End If
    End If
            
    If Target.Column = 1 And Len(Target.Value) > 0 Then
        lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
        dblMyCount = WorksheetFunction.CountIf(Range("A1:A" & lngLastRow), Target.Value)
        'If the entry has been duplicated, then...
        If dblMyCount > 1 Then
            '...inform the user, clear the entry and quit
            MsgBox Target.Value & " has already been entered." & vbNewLine & "As such cell " & Target.Address(False, False) & " will be cleared.", vbCritical
            Application.EnableEvents = False
                Target.ClearContents
            Application.EnableEvents = True
            Exit Sub
        End If
        If InStr(Target.Value, "-") > 0 Then
            strMyKey = Evaluate("LEFT(""" & Target.Value & """,FIND(""-"",""" & Target.Value & """)-1)")
        Else
            strMyKey = Target.Value
        End If
        For Each rngMyCell In Range("A1:A" & lngLastRow)
            If rngMyCell.Address <> Target.Address Then
                If Len(rngMyCell) > 0 Then
                    If Left(rngMyCell.Value, Len(strMyKey)) = strMyKey Then
                        MsgBox "You cannot use " & Target.Value & " as " & strMyKey & " is already in use." & vbNewLine & "Cell " & Target.Address(False, False) & " will be cleared.", vbCritical
                        Application.EnableEvents = False
                            Target.ClearContents
                        Application.EnableEvents = True
                        Exit For
                    End If
                End If
            End If
        Next rngMyCell
    End If

End Sub

Robert