Try this.
Make a dynamic named range, anywhere in your workbook, that contains all your unique values.
This list must be continuous, i.e. no blanks.
It need not be sorted, but it is better that it is.
e.g.
Formulas > Name Manager ...
Name:= "Unique_List"
Refers To:=
You can add to, subtract from, or amend this list as required, just don't leave any blanks.
Note
Any changes made to the list will not be reflected in Column C if their values have been entered before the changes were made.
I have amended the code to use this named range, see the attached workbook.
This will slow down considerably if you are talking of thousands of unique values.
You will no doubt need to change the Ranges in the code to suit your sheet layout.
These changes need only be made in the Sub "Worksheet_Change"
Bookmarks