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.
delete rows in excel data list
woodlot4 wrote on August 24, 2005 21:20 EST
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]
Duplicates When Entering Data
Mike wrote on December 31, 1969 19:00 EST
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
vbaq wrote on December 31, 1969 19:00 EST
how do i use vba to code a input box to prevent duplicate data from being entered.
RE: Preventing Duplicate Data using VBA
John wrote on December 31, 1969 19:00 EST
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
VBA code to prevent duplicate entries
cart0250 wrote on November 19, 2006 01:54 EST
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
Carim wrote on November 19, 2006 13:27 EST
See answer in your other post ...
Carim
:)
Love the VBA code
ByTheLake wrote on November 27, 2006 16:52 EST
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
Opps Sorry Let me be more specific
ByTheLake wrote on November 27, 2006 17:06 EST
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!!
queries
Lilly wrote on December 31, 1969 19:00 EST
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
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
The New Financial Order: Risk in the Twenty-First Century
