How to input only unique values in a column in Excel 2007

30
2014-03
  • ray247

    I'd like to know the easiest way to make sure that only unique strings are entered in a particular column in MS Excel 2007? I know I have to put a formula in the Data > Data Validation, but I don't know how.

    Say, I'm entering data anyplace within column B, and I want to know if the inputting value is already elsewhere in the column (above or below the current cell) and don't allow if there is a duplicate.

    Edit:

    1. I've seen answers like this one on this site, but they all have an range say from B1 to B20, how do I have this validation on the entire column, is this possible?

    2. Seem like when I drag on the cell, duplicated values can appear, it is only when I try to input the value that this validation works. So, how do I restrict on the dragging as well as manual input on this?

    • Answers
  • goblinbox

    Instructions for avoiding duplicates for Excel 2000 are here.

  • Jason Ellis

    Also, just a side note.... when referring to particular cells in a column (like in your example) you would refer to them as B1:B20. To refer to the whole column, you can use B:B. That describes an array of all data in the B column. Hope that helps.

  • Doug Glancy

    You can only deal with copying over validation with VBA:

    http://spreadsheetpage.com/index.php/tip/ensuring_that_data_validation_is_not_deleted/

  • Toc

    I answer only your question 2. You have to write your own Private Sub Worksheet_Change routine.


    • Related Question

    vba - Excel: Update a value in a named range
  • Vinzz

    I've got some cells in an Excel doc populated from a named range (data/validation/list source=MyNamedRange):

    Sample

    Here, the A1:A3 range is named Foobar

    B5:B6 is under data validation with it's source set to 'Foobar'

    I'd like to be able to update cell's A2 content, from Bar to Quux, and see automatically B5 cell's content updated to Quux, as it's source has been changed.

    It might be performed through a macro, but I don't know how to code this.

    Any hints please?


    • Related Answers
  • dkusleika

    This seems dangerous, but I can't see any problem with it. Basically if you change anything in Foobar, it searches every cell on the sheet that has Data Validation. If the DV points to Foobar and the value isn't in the list, then it must have been the value that was changed. It worked with my limited testing. Let me know if you see any flaws.

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rCell As Range
    Dim rFound As Range

    'Only run this when a cell in Foobar is changed
    If Not Intersect(Target, Me.Range("Foobar")) Is Nothing Then

        'Go through every data validation cell in the sheet
        For Each rCell In Me.Cells.SpecialCells(xlCellTypeAllValidation).Cells

            'if the DV in the cell points to foobar
            If rCell.Validation.Formula1 = "=Foobar" Then

                'See if the cell's value is in the Foobar list
                Set rFound = Me.Range("Foobar").Find(rCell.Value, , xlValues, xlWhole)

                'If it's not in the list, it must be the one that
                'changed, so changed it
                If rFound Is Nothing Then
                    Application.EnableEvents = False
                        rCell.Value = Target.Value
                    Application.EnableEvents = True
                End If
            End If
        Next rCell
    End If

End Sub

    Note that this goes in the Worksheet's module, not a standard module. As always, test code on a copy of your workbook.

  • Lunatik

    You would need to use VBA to accomplish this, or have an extra calculation cell for each of B5:B6 that would detect that the cell value is no longer contained within the named range and flag this.

    I use to have to maintain a huge workbook that had thousands of such dependencies, it was real nightmare to debug.

    This link has more on ways of getting round the one-way nature of the link between validation source range and the target cell.

  • Martin

    What you are doing now is store the value of A2 in B6. But you need to store a reference to A2. Then, B6 would update automatically. I think "=A2" (without quotation marks) as the field value should do this.