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

 

[Aladin Akyurek]

Create a distinct items list on Sheet1, from items in column A. The distinct items list should then become the source for data validation.

 

[sujittalukde]

This cannot be done as It is a part of an overall project and also the items list will not end here new data and items will be added frequently then making such a list separately is not possible.

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

 

[Aladin Akyurek]

This cannot be done as It is a part of an overall project and also the items list will not end here new data and items will be added frequently then making such a list separately is not possible.

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

Alas, the dinner cannot be served the way you wish... You could of course search the board for a VBA solution.

Are you on Excel 2003?

 

[sujittalukde]

Can a link be provided?

 

[Aladin Akyurek]

Can a link be provided?

Are you on Excel 2003 or beyond?

 

[sujittalukde]

Excel 2003

 

[Aladin Akyurek]

Excel 2003

Book3
	A	B	C	D
1		6
2	Items	Distinct Items
3	fga	fga
4	dfs	dfs
5	gda	gda
6	gda	tra
7	tra	tur
8	dfs	wra
9	tur
10	wra
11
Sheet1

Select the current range A2:B10 and activate Data|List|Create List in order to convert the range into a list. Now you are free to add new items at the insertion point (indicated with *) in column A.

B1:

Control+shift+enter...

=SUM(IF(FREQUENCY(IF(A3:A10<>"",MATCH("~"&A3:A10,A3:A10&"",0)),ROW(A3:A10)-ROW(A3)+1),1))

B3:

Control+shift+enter...

=IF(ROWS($B$3:B3)<=$B$1,INDEX($A$3:$A$10,SMALL(IF(FREQUENCY(IF($A$3:$A$10<>"",MATCH("~"&$A$3:$A$10,$A$3:$A$10&"",0)),ROW($A$3:$A$10)-ROW($A$3)+1),ROW($A$3:$A$10)-ROW($A$3)+1),ROWS($B$3:B3))),"")

and copy down.

MyList (for data validation) is defined by:

=OFFSET(Sheet1!$B$3,0,0,Sheet1!$B$1)

 

[sujittalukde]

But this is also using separate column for which I am facing difficulty. Else, can this be made through macro?

 

[Krishnakumar]

Hi,

Draw a combobox from Control Toolbox.

Paste this code in the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, v, z
If Target.Column <> 1 Then Exit Sub
a = Range("a1", Range("a" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
    .comparemode = vbTextCompare
    For Each v In a
        If Not IsEmpty(v) And Not .exists(v) Then
            .Add v, Nothing
        End If
    Next: z = .keys
End With
With Me.ComboBox1
    .List = Application.Transpose(z)
End With
End Sub

HTH