Preventing duplicates in a column regardless of the case of the entry

prevent duplicate entries in multiple columns in excel
how to stop text repeating in excel
excel prevent duplicate text entries
how to prevent duplicate value in excel
prevent duplicate entries in excel drop down list
excel prevent duplicate conditional formatting
prevent duplicate entries in google sheets
how to avoid duplicate entries in excel worksheet while transferring data from userform

I type three entries in specific cells [A2,B2,C2] and run code to take this data to the first empty row in a table.

The code also prevents duplicates based on the entered value in cell B2. If it already exists in the range (B2:B5000) it prevent duplicates.

The problem is it does not ignore the case.

For example: I enter value "Acetic Acid" After awhile I add "acetic Acid" or change any letter case.

The code adds it normally without preventing.

How do I ignore the letter case?

Sub tarheel()
LastRow = Range("A10000").End(xlUp).Row + 1
LR = Range("b10000").End(xlUp).Row + 1
For r = 5 To LR
    If Cells(r, 2) = Range("b2") Then MsgBox "This Item Name already exist, No shift will done": Exit Sub
Next
Cells(LastRow, 1).Value = Range("A2").Value
Cells(LastRow, 2).Value = Range("B2").Value
Cells(LastRow, 3).Value = Range("C2").Value
Range("A2:C2").Select
Selection.ClearContents
Range("A2").Select

End Sub

To change case in VBA, you have LCase and UCase, which will respectively change all of your string into lower case or upper case.

Here is your code with the change and got ride of the useless (and ressource-greedy) select at the end :

Sub tarheel()

    LastRow = Range("A10000").End(xlUp).Row + 1
    LR = Range("b10000").End(xlUp).Row + 1

    IsIn = False

    For r = 5 To LR
        If LCase(Cells(r, 2)) = LCase(Range("b2")) Then _
            MsgBox "This Item Name already exist, No shift will done": Exit Sub
    Next

    Cells(LastRow, 1).Value = Range("A2").Value
    Cells(LastRow, 2).Value = Range("B2").Value
    Cells(LastRow, 3).Value = Range("C2").Value

    Range("A2:C2").ClearContents
    'Range("A2").Select

End Sub

How to prevent duplicate entries in Excel column, only unique data , Select the entire column where you need to avoid duplicates. Click on the first cell with data keeping the Shift keyboard button pressed and then� With Kutools for Excel, you can prevent duplicate entries with one click. Please do as this: 1. Select the range that you want to prevent duplicate entries. 2. Click Kutools > Prevent Duplicate Entries, see screenshot: Now when you enter the same data with the first data in the range, you will get the following warning:

thanks for all your replies and i will try it too and give feedback to you.

i could figure it out by adding this line at the top of my module.

Option Compare Text

and it fixed my problem.

thanks

2 Easy Methods to Prevent Duplicate Values in a Column in Your , If you want to avoid entering duplicate values in a column in Excel, you can refer to this article. After that, input the following formula in the “Formula” field. Since then, every time when you enter a value in any cells, no matter in any columns, the macro will auto Custom Alert in Case of Duplicate Values in a Column� But prevention is better than the cure, so let’s look at how to prevent duplicates in Excel. We can use the Data Validation tool with a formula to stop duplicates from being entered by the user. In this example we want to prevent duplicates from being entered into column A. Select the whole of column A by clicking the column header

You can replace your loop that compares for existing values with a case insensitive one by forcing both values to either upper or lower case.

For r = 5 To LR
    If lcase(Cells(r, 2)) = lcase(Range("b2")) Then
        MsgBox "This Item Name already exist, No shift will done"
        Exit Sub
    end if
Next

It may be more efficient to use a case-insensitive worksheet function to check the whole range at once.

If cbool(application.countif(Range("B5:B" & LR), Cells(r, 2))) Then
    MsgBox "This Item Name already exist, No shift will done"
    Exit Sub
end if

Another possible:

If not iserror(application.match(Cells(r, 2), Range("B5:B" & LR), 0)) Then
    MsgBox "This Item Name already exist, No shift will done"
    Exit Sub
end if

Excel Data Validation - Duplicate Entries, This example shows you how to prevent duplicate entries (both text and numbers ) from being entered into In this case the whole of column B. Let’s take an example to understand how and where we can prevent the duplicate entries. We have a data of Mobile nos. In which we want to stop the duplicate entries of mobile numbers. To preventing duplicates while entering in the data follow below given steps:-Select the data range of Mobile number in the range B2:B10.

Sub tarheel()

    LastRow = Range("A10000").End(xlUp).Row + 1
    LR = Range("b10000").End(xlUp).Row + 1

    IsIn = False

    For r = 5 To LR
        If LCase(Cells(r, 2)) = LCase(Range("b2")) Then _
            MsgBox "This Item Name already exist, No shift will done": Exit Sub
    Next

    Cells(LastRow, 1).Value = Range("A2").Value
    Cells(LastRow, 2).Value = Range("B2").Value
    Cells(LastRow, 3).Value = Range("C2").Value

    Range("A2:C2").ClearContents
    'Range("A2").Select

End Sub

Excel Data Validation - Prevent Duplicates, This example shows you how to prevent duplicate entries (both text and numbers ) from being entered into In this case the whole of column B. To check this, select cell A3 and click Data Validation. As you can see, this function counts the number of values in the range A2:A20 that are equal to the value in cell A3. Again, this value may only occur once (=1) since we don't want duplicate entries. 6.

How to prevent duplicate entries in a column in Excel?, Is there a way to allow certain values or in my case text to be duplicated while everything else is not allowed? Reply. We'll use data validation to prevent duplicate numbers in the Membership Number column. First, enter the following function into cell K3: =COUNTIF(Table1[Membership Number],B3)

How to remove duplicates that are case sensitive in Excel?, Normally, the Remove Duplicates feature in Excel can help you remove the duplicate values The following formula may help you to remove the case sensitive duplicate values and keep the first, Prevent Duplicate Cells; Compare Ranges. Since the data validation feature in excel can only prevent duplicate entries in a column if you actually click into a cell (and doesn't work if you use copy/paste), I would like to be able to work around this using VBA coding.

Preventing Duplicates While Entering Data in Excel 2010, If that is not checked, you would be able to enter duplicate entries. Also, in case you are trying to copy and paste the value from some other cell or drag the formula� I am working with Salesforce IDs which are case Preventing duplicates in a column regardless of the case of the entry From Current Cell find the Most Previous

Comments
  • This topic looks like it may solve your problem: [stackoverflow.com/questions/17035660/… [1]: stackoverflow.com/questions/17035660/…
  • Indeed interesting link, if you don't have both case sensitive and insensitive this is interesting. If you have, you can just use LCase() as described in my answer