YouTubersLoveExcel#41: Prevent Duplicates COUNTIF Data Valid

Hi!
I want to use data validation to prevent duplicate values inserted.

I have different categories in column A, with their respective numbers in column B, see the sample.xlsx attached.

How to apply data validation so that you can have the same category entered several times, but only a unique number associated with it?



I've tried with cond.formatting using (using Norwegian syntax):
=COUNTIF($A$1:$A$15;A1;$B$1:$B$15;B1)>1 (don't know if this works)
=ANTALL.HVIS.SETT($A$1:$A$15;A1;$B$1:$B$15;B1)>1 (Norwegian formula)
and it works in some way, but it only highlights both the duplicates - while I prefer only the last entry.

Hello I am currently trying to use Excel 2003 Data validation to prevent duplicate data Entry.

The data I have for example in A1:A4 is as follows

04-20-026-22W4/2
16-33-025-22W4/2
07-27-026-22W4/0
04-20-026-22W4/0

In the data validation I have custom and this for the formula

=COUNTIF($A$1:$A$4,A1)<=1 for cell A1

My problem arises in that I need it to do this lookup based on the wildcard for the last number.

So that cell A1 = A4 and therefore will not let me put in 04-20-026-22W4/0 or /2 or anything else.

So something similar to 04-20-026-22W4/* where * = wildcard.

The cells are currently formatted as General and I have tried a few variations of countif and sumproduct with no luck.

Thanks in advance for any help you can offer.

Hi,

I'm quite new to microsoft excel.
First of all I will tell you what I am using excel for and what I need.

What I do: I have a company and I am listing all of my clients in excel. I ask them for their name, birth date, IP, email adress .....

What I need: Well I don't want to have double accounts which means I don't want members to register again with the same email and/or the same IP. I don't want to check that manually each time I get a new client. Isn't there an option or excel addon software for such things?

I want excel to give me some sort of sign if I write the same name/ip/email adress again.

I hope you understand what i want.
Thank you in advance

I have the following code which works fine. I now want to add some more code that will look to see if the data already exists and if it does I want to give the user the choice of canceling the operation or if they wish to continue I want the old data to be deleted so that there is only one unique record. The values in colloum "A" are dates and there should only be one instance of.

Sub CopyData()
'
' CopyData Macro
' Macro recorded 11/08/2008 by Dave
If Application.CountA(Range("D2")) = 0 Then MsgBox ("Please Enter A Date")
If Application.CountA(Range("D2")) = 0 Then Exit Sub
If Application.CountA(Range("D3")) = 0 Then MsgBox ("Please Enter Flour Temp")
If Application.CountA(Range("D3")) = 0 Then Exit Sub
Application.ScreenUpdating = False
Sheets("Data").Visible = True
Application.Goto Reference:="CopyData"
Selection.Copy
ActiveWindow.SelectedSheets.Visible = False
Sheets("Archive").Visible = True
Sheets("Archive").Select
Range("A7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("6:6").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("B11").Select
Sheets("Archive").Visible = False
Sheets("Data").Visible = False
Sheets("Data Input").Select
Range("D11:H25").Select
Selection.ClearContents
Range("D3").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=21
Range("D32:H46").Select
Selection.ClearContents
Range("G32").Select
ActiveCell.FormulaR1C1 = "=R[-21]C"
Range("G33").Select
ActiveWindow.SmallScroll Down:=18
Range("G32").Select
Selection.AutoFill Destination:=Range("G32:G46"), Type:=xlFillDefault
Range("G32:G46").Select
Range("F38").Select
ActiveWindow.SmallScroll Down:=-30
Range("D2:E2").Select
Range("C11:H25").Select
ActiveSheet.Unprotect
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.SmallScroll Down:=27
Range("C32:H46").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.SmallScroll Down:=-36
Range("D2:E2").Select
Sheets("Yield").Visible = True
Application.Goto Reference:="YieldData"
Selection.Copy
Range("D8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("5:5").Select
Range("D5").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
ActiveWindow.SelectedSheets.Visible = False
Sheets("Data Input").Select
Range("D2:E2").Select
Selection.ClearContents
Application.ScreenUpdating = True
End Sub

I know it's pretty simple to prevent duplicates in a column, using data validation or VBA - but how do you do it across multiple cells?

say I've got a spreadsheet where A2 is Surname, B2 is Forename, and C2 is a date value, I want to prevent users duplicating all three.

So each column can contain duplicates, but there cannot be the same combination of each three appearing more than once. The simplest thing I though would be to concatenate (A2,B2,C2) and stick custom validation on the resulting formula, but that didn't seem to work. I've also tried a few VBA bits I picked up from various online places but they all relate to single columns it seems.

any ideas?

I have copied this macro and applied it to my sheet but I only want it to prevent duplicates in column C
not being well versed in macros I'm not sure what to change

PHP Code:

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 "P.O.# has been used: " & c.Address(0, 0) 
Target.Value = "" 
End If 
End With 
End Sub 




this code prevents duplicates in all columns

Hi,

I am trying to add values to combobox from Form.
Can any body tell me how to prevent addition of duplicates value in combobox.
I have attached the excel I am working upon.

Thanks

Not sure if this can be done but...

I am aware how to prevent duplicates in Excel but is there any way that Excel can prevent duplicates being selected from the Drop Down List Function?

Basically I have used the Data Validation Tool to create a Lookup List where the User can then select data items from the Drop Down List. What I would like to do is restrict the User from selecting the same item in the drop down list that has been selected previously.

I could obviously apply some Conditional Formatting on top of the list function to highlight duplicates but wondered if there was a more direct approach through the data validation option in Excel?

HI ,
I would like to know how to prevent entering duplicates in excel sheet. Basically I have some Numbers in a list & sheet can have only one instance of these numbers. It should stop entering the 2nd.

eg- if Number 10 is existing in the sheet , we should not be able to enter that again in to the sheet.

Pls help me on this .....Tks in advance...


G21

I have a spreadsheet that users need to enter part numbers that we have quoted for. I need to prevent users from entering duplicates in column C. Data validation isn't an option since users copy & paste sometimes into that column.

The code works fine if user happens to copy & paste a part number already on that list. But for some reason, sometimes the code will work & sometimes it fails to catch duplicates when users type in the part numbers. Sometimes users will insert a row & type a part number & the code won't catch the duplicate part number.

Need help with the code to catch any duplicates in column C & sometimes the data in column C is non contiguous so the code has to check if the data entered matches any cell in that column. I would also like for the message box to also display the cell location were the original data is located.

Below is the code used & I also attached a sample of my spreadsheet.

Any help is greatly appreciated!

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range
Dim ans As String

Const myCol As Long = 3

If Intersect(Target, Columns(myCol)) Is Nothing Then Exit Sub

Application.EnableEvents = False

For Each r In Intersect(Target, Columns(myCol))
   If Application.CountIf(Columns(myCol), r.Value) > 1 Then
   MsgBox (r.Value & " already exists")
       r.ClearContents
   End If
Next

Application.EnableEvents = True

End Sub