Unique Entries in All Cells for 1 Column

[Anna920]

Hello All,

I am not sure whether there is a way to do what I'm thinking of, but I wanted to reach out to this community in case anyone has any ideas.

I have a spreadsheet in which at the end of every row, there is a column which is supposed to contain a unique reference number. However, this is a spreadsheet which is updated by several individuals and I've noticed that some of the reference numbers are being duplicated.

Is there any way that I can set that column to only allow unique entries into each cell?

Thank you for any assistance that you're able to provide.

 

[number kruncher]

In the cell to the right of the Reference, you could have a formula, to Count it's existence in the list.

You could then set a Worksheet Change macro to prompt if this value Counts >1

This would not stop you from entering a duplicate entry, but, propmt you to alter it.

 

[MickG]

The code below will delete any duplicate as you enter it in Column "G", and show as msgbox if a duplicate is found.
Alter column in code To suit.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Columns("G:G")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(1, Target.Column), Cells(rows.Count & Target.Column).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.value) [COLOR="Navy"]Then[/COLOR]
                .Add Dn.value, ""
            [COLOR="Navy"]Else[/COLOR]
                MsgBox "The value " & Dn.value & " [COLOR="Navy"]Is[/COLOR] a Duplicate"
                Dn.value = ""
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

Just as a postScript, as this is your first post (Welcome to the board).
To insert your code:-
Right click you sheet tab, Select "ViewCode". VB Window appears.
Paste code into VB Window.
Close the VB window.
To run code, alter a value in Column "G" (or the column you have altered it to) to a duplicate value.
Regards Mick