Validation list to show unique items only

[sujittalukde]

I am using col A as the source list for Data validation list. In col A some items repeat more than once. The problem is that the list also shows the items as appearing in the col A ie if item "A" is appearing for more than once, in the list also it is appearing more than once.

I want that the list should not show an item more than once.

How this can be done?

A snapshot is given below:

Book4
	A	B	C	D
1	A	Select at C1	A
2	B
3	C	Name = mylist
4	A	Name range formula
5	C	=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))
6	D
7	F	Problem: Cell A1 is the data\validation\list
8	B	Source: mylist
9	E	The list at Cell A1 shows duplicates also
10	F	ie A is showing for the times it is in the col A
11	R	I want that the list should show only
12	T	unique items ie all the items in the list once.
13	S
Sheet1

 

[Tom Urtis]

I want to make a formula from the existing list so that validaiton list will show only unique items.

Right click on your Sheet1 tab (this is the sheet, according to your screen shot, where the source list is in column A and where cell C1 is being validated), left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

To get the code started the first time, make any change to column A in Sheet1, which can be as simple as selecting any cell in column A (note, that's any cell, an empty cell is OK as long as it is in column A), and pressing the Delete key, or the F2 key and Enter key. Thereafter, cell C1 will always show an updated current unique list in its validation drop-down list. The list will be updated whenever you change a cell in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False
Dim cell As Range, strVal$
strVal = ""

On Error Resume Next
For Each cell In Columns(1).SpecialCells(2)
With cell
If WorksheetFunction.CountIf(Range("A1:A" & .Row), .value) = 1 Then
If strVal <> "" Then
strVal = strVal & "," & .value
Else
strVal = .value
End If
End If
End With
Next
Err.clear

If strVal = "" Then
Range("C1").Validation.Delete

Else

With Range("C1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=strVal
.IgnoreBlank = True
.InCellDropdown = True
.ErrorTitle = "No such animal"
.ErrorMessage = "Select an item from" & Chr(10) & "the drop down list."
.ShowInput = True
.ShowError = True
End With

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[sujittalukde]

Thanks Tom, code is working perfectly.

Dear Kris, some more clarification is needed for your code. I am pasting the code in the sheet module and putting the Combo box in that sheet. then clicking the combo box arrow, nothing in the list??????????