PDA

View Full Version : [SOLVED] Data Validation



Philcjr
08-19-2005, 02:24 PM
Situation:

I have a list of numbers in Column A (about 300+)
Is there a way to ensure that the user can not type in a number that is already been used? (Rows 300+ an foward)
Lastly, I need to verify the current data, that the numbers that are there have not been duplicated
The DataValidation feature in Excel would be GREAT to use, as I could use the auditing feature to have excel circle the invalid data... however I have not idea how to formulate this one

VB code would be good too...

Anyone with some thoughts?

Thanks,
Phil

Jacob Hilderbrand
08-19-2005, 02:44 PM
Select column A and make sure that A1 is active.

Data | Validation | Custom


=COUNTIF($A$1:$A1,$A1)=1

To check for previous duplicates, in B1 put this.


=IF(COUNTIF($A$1:$A1,$A1)>1,"Duplicate","Not a Duplicate")

Fill Down

MWE
08-19-2005, 02:49 PM
Situation:



I have a list of numbers in Column A (about 300+)
Is there a way to ensure that the user can not type in a number that is already been used? (Rows 300+ an foward)
Lastly, I need to verify the current data, that the numbers that are there have not been duplicated

The DataValidation feature in Excel would be GREAT to use, as I could use the auditing feature to have excel circle the invalid data... however I have not idea how to formulate this one

VB code would be good too...

Anyone with some thoughts?

Thanks,
Phil
I would NOT do this with the DataValidation feature in Excel even if you could figure out the rules. The DataValation feature can be "overridden" with a simple copy/paste into a cell where DataValidation has been set.


Rather, I would do this with the worksheet procedure Worksheet_Change. It resides in the code module for the particular worksheet and executes each time anything is changed in that sheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
statement
statement
.
.
.
End Sub

The code would test to see if the recent change was to one of the cells of concern. If so, it would do the checks you want. If there is a problem, it could set cell/font colors, display messages, call the user nasty names, wipe out the hard drive, ... whatever seems most reasonable.

You could integrate your second need (to check everything at the end) into the above and simple check everything every time. That is not very efficient, but would work. Alternatviely, a second procedure (which could also be triggered by certain "events" or manualy envoked) would check everything.

I am assuming some level of VBA skills on your part. If this approach makes sense and you are not quite up to the task, post back and I or someone else can rough out some code for you.

mdmackillop
08-20-2005, 02:06 AM
Hi Phil,
Paste the following in the ThisWorkbook module. Is should identify duplicates in PRECEDING cells in Sheet1 (change as required), the actions can be modified as suggested by MWE. If you need to check against the whole list for inserted changes, let us know.
Regards
MD


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ChkList As Range
Dim Test As Long
Dim MySheet As String
MySheet = "Sheet1"
If Not Intersect(Sheets(MySheet).Range("A:A"), Target) Is Nothing Then
Range("A:A").Interior.ColorIndex = xlNone
Set ChkList = Sh.Range("A1:A" & [A65536].End(xlUp).Row())
Test = Application.WorksheetFunction.Match(Target, ChkList, 0)
If Test = Target.Row() Then
MsgBox "OK"
Else
Cells(Test, "A").Interior.ColorIndex = 6
Target.Interior.ColorIndex = 6
MsgBox "Duplicate in row " & Test
End If
End If
End Sub

Philcjr
08-22-2005, 06:00 AM
Thank you all for your help. I am sorry I have not replied earlier, my Internet Provider was down on over the weekend and then was having some computer issues. All is OK now.

I will be using DRJ's reply to verify the current data and use MD's reply for future entries. I will let you all know how it turns out tonight after work.

Thanks for all your help

Phil