Validation List with out Duplicates

[zhongmengkai]

I am trying to find a way to create a validation list where the source is linked to a list that has duplicates. All of the duplicates are bundled together and I do not want to remove the duplicates from the source. I just want to remove them from the drop down list.

Source:$A$1:$A$10
List: $C$1:$C$10

___|______A______|__B__|______C______|
_1_|____ Home____|_____|____ Time ____|
_2_|____ Home____|_____|____ Home____|
_3_|____ Home____|_____|____ Camp____|
_4_|____ Home____|_____|____ Camp____|
_5_|____ Camp____|_____|____ Time ____|
_6_|____ Camp____|_____|____ Home____|<______
_7_|____ Camp____|_____|___ _ _ _ _ ___| | Home |
_8_|____ Time ____|_____|___ _ _ _ _ ___| | Camp |
_9_|____ Time ____|_____|___ _ _ _ _ ___| |_Time_|
10_|____ Time ____|_____|___ _ _ _ _ ___|

I am fine with using macros of creating functions or preferably there is already a function to do this that I don't know about.

 

[jeffreybrown]

You can run this macro which will create a unique list in column C...

Sub AdvFilter()
    Range("A1", Range("A" & Rows.Count).End(xlUp)).AdvancedFilter _
            Action:=xlFilterCopy, CopyToRange:=Range("C1"), Unique:=True
End Sub

...then in the name manager create a name and as the source

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,MATCH(REPT("Z",255),Sheet1!$C:$C))

Excel Workbook
	A	B	C
1	Type	Home	Type
2	Home		Home
3	Home		Camp
4	Home		Time
5	Home
6	Camp
7	Camp
8	Camp
9	Time
10	Time
11	Time
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[jeffreybrown]

Also as an aside...

It will help tremendously when you are posting a screen shot of your data to use one of the HTML maker's below so the data can easily be copied for testing.

http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or

http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2