» Preventing Duplicates When Entering Data

This looks very close to what I have been trying to find for months. What if I want to delete the row that has duplicate information in it? How can I do a formula that will do that?

[QUOTE=ExcelTip]Problem:

We want to make it impossible to enter duplicate values in List1 (cells A2:A10).

Solution:

Use Data Validation as follows:
1. Select the range to validate (cells A2:A10)
2. Data --> Data Validation --> Settings
3. From the Allow dropdown list, select Custom.
4. Enter the following COUNTIF formula in the Formula box:
=COUNTIF($A$2:$A$10,A2)=1
5. Select the Error Alert tab.
6. In the Title box, enter "Duplicate Entry".
7. In the Error Message box, enter "The value you entered already appears in the list above."
8. Click OK.

After following this procedure, any duplicate entry will cause the error message to pop up, forcing the user to enter a different value.[/QUOTE]

THis looks close to what I have been trying to find for months but what if I want to delete duplicates after pasting instead of making an error code? For instance, I have a list of names and addresses and I want to delete any entire row with a duplicate address. How can this be done?

how do i use vba to code a input box to prevent duplicate data from being entered.

To prevent duplicate entries it would be easier to write code in VBA, such as the below which prevents the user from entering duplicate entries in all columns except A and B. Add this to the ThisWorkbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If IsEmpty(Target.Value) Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Column < 3 Then Exit Sub
With Range(Cells(1, Target.Column).Address & ":" & Cells(Target.Row - 1, Target.Column).Address & "," & Cells(Target.Row + 1, Target.Column).Address & ":" & Cells(Rows.Count, Target.Column).Address)
Set c = .Find(Target.Value, , , xlWhole)
If Not c Is Nothing Then
MsgBox "Preference already exists at range: " & c.Address(0, 0)
Target.Value = ""
End If
End With
End Sub

It is more efficient to use VBA code to prevent duplicate entries within each column. For example, the below code prevents duplicate entries in all columns except column A for each sheet within a workbook. Insert it in the ThisWorkbook module.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If IsEmpty(Target.Value) Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Column < 2 Then Exit Sub
With Range(Cells(1, Target.Column).Address & ":" & Cells(Target.Row - 1, Target.Column).Address & "," & Cells(Target.Row + 1, Target.Column).Address & ":" & Cells(Rows.Count, Target.Column).Address)
Set c = .Find(Target.Value, , , xlWhole)
If Not c Is Nothing Then
MsgBox "Data already exists at range: " & c.Address(0, 0)
Target.Value = ""
End If
End With
End Sub

See answer in your other post ...

Carim
:)

O love the VBA code...For the error message, instead of stating where the error is, what needs to be placed in the code the return the Value of column A and column B for that particular row?

Thanks

Sorry , Let me be more specific I wanted to have C1 equal A1&B1, C2 equal A2&B2 etc..... so when the C column has a duplicate value, I want the error message to state that ('the value of column A' has used 'the value of column B'....That would be great . What I have is about 50 users that can select 50 different options. I want the ability to see if a user is about to sleect the same option....

Thanks!!

Will an expert pl help me on the following?

1) I need to enter data onto an excel sheet using a form designed by me. I need to know the codes that I need to write in VB for a particular text box to prevent duplicate entries in only a particular column say colum B of sheet 3. This column is going to have only unique values. ie. when data is entered into that text box, it should check the particular column and throw up a msg saying that this value already exists.

2) Similarly I need the VB codes to delete a particular row of information that has already been entered in the sheet using the form.

3) I need to write a function in one of the cells in a particular sheet (say sheet 3, L2 to check if the value that has been entered in another cell (say sheet 3 D2) falls in a particular category. I have entered the lists of items in separate columns in sheet 1. Each column in sheet 1 is a category. Now as soon as a value is entered in D2, the function should check if it exists in any of the columns in sheet 1 and display in sheet 3 L2, the categories name. How do i do this?

Pl email me the solutions at cutelilly_04@yahoo.com