MS Excel Macro for validating unique entries in the column

[technowerk]

Hi,
I need to validate a column in Excel for its uniqueness. This column should accept only unique values. The function should iterate through the entire column to compare the string with all the previous and next cells in the column. How can i do this using a function n not any formula?
Also i'd appreciate if somebody can help me with an Excel sheet with 5 columns such that only 1 of the columns has a '1' n rest all can have only '0's.
What i mean is suppose u have columns A-E. If u put a '1' in cell A2, rest all ie B2,C2,D2,E2 must have only '0's n no other value. So that only 1 of 5 cells has a '1'. Next time u may choose C5 to put '1'.Then A5,B5,D5,E5 must have only '0's.
Thanks in advance.

[Hack]

Moved to Office Development.

[DKenny]

OK, lets do this one question at a time.

I need to validate a column in Excel for its uniqueness. This column should accept only unique values.

The following function will check for uniqueness in a sinngle column. The Worksheeet_change event will fire whenever a value is entered into a cell. In my example I am only checking for unique vales in column 5, but you can check in one, many or all columns.

I will answer your secong question in a seperate post.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
        
    'Assuming you only want to test for uniqueness in Col 5 ("E")
    If Target.Column = 5 Then
    'If the value being entered is a duplicate in the column then remove it
        If Not Unique(Target.Column) Then
            Application.EnableEvents = False
            Target = ""
            Application.EnableEvents = True
        End If
    End If
    
End Sub
 
 
Private Function Unique(ColNum As Integer) As Boolean
Dim RowCount As Integer
Dim RowNum As Integer
Dim ChkVal As String
Dim ChkCount As Integer
    
    'Assume there are no duplicates
    Unique = True
    
    'Determine how many rows to test
    RowCount = ActiveSheet.UsedRange.Rows.Count
    
    'loop through each cell in the target column
    For RowNum = 1 To RowCount
        'Get the value in the cell being tested
        ChkVal = ActiveSheet.Cells(RowNum, ColNum)
        'Count the occurances of that value in the column
        If ChkVal <> "" Then
            ChkCount = Application.WorksheetFunction.CountIf(ActiveSheet.Columns(ColNum), ChkVal)
        Else
            ChkCount = 1
        End If
        'If the value occurs more that once set unique to false and exit the loop
        If ChkCount > 1 Then
            Unique = False
            Exit For
        End If
    Next RowNum
End Function

[DKenny]

i'd appreciate if somebody can help me with an Excel sheet with 5 columns such that only 1 of the columns has a '1' n rest all can have only '0's.

Again we will use the Worksheet_Change event to trigger this code. The code only runs when the user edits a cell in the first 5 columns. It also checks to ensure that only one cell is being edited.

If the cell value is zero or is an empty cell it does nothing.
If the cell value is other that 1, 0 or empty it clears the cell.
If the cell value is 1 it loops through the first 5 cells in the row of the target cell and sets their value to 0, except for the target cell.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColNum As Integer
    
    'only run this check if editing the first 5 columns and only editing a single cell
    If Target.Column <= 5 And Target.Cells.Count = 1 Then
        Select Case Target.Value
            Case 0
                'do nothing
            Case 1
                'Loop throught the 5 columns
                For ColNum = 1 To 5
                    'You don't need to change the current cell
                    If ColNum <> Target.Column Then
                        'Change the other 4 cells to 0
                        Cells(Target.Row, ColNum) = 0
                    End If
                Next ColNum
            Case Else
                ' If a value other than 1 or 0 is entered, remove it. (This is optional - I don't know if this is a reqirement)
                Application.EnableEvents = False
                Target.Value = ""
                Application.EnableEvents = True
        End Select
    End If
End Sub

[technowerk]

hey DKenny,
thanks a lot for ur quick reply. I really appreciate that. The codes u sent me work fantastic n produce the desired functionality. No doubt about it. However I once again need ur help to modify the codes so as to run them in separate macros n not as a "worksheet_change" event b'coz i want the users to fire the event to chech the column's validity. I don't want it to happen as n when the users enter the data.
Hope u will help me with this problem also as u did earlier. Looking forward to ur guidance.

Thank you once again.

[DKenny]

You could add a couple of buttons to the worksheet and move the code into the "_click" events.

[technowerk]

hey,
It works fantastic. Thank you so much for sparing ur time for me. Hope to receive more guidance from you in the future as well.

Thanks once again.