Data Validation List: each value once!

[arjanz]

I'm having troubles creating a Data Validation List from a range. I have a list with:

All
All
Various
Various
Various
Finance
Exploration
Exploration
Exploration
Exploration
Exploration
Exploration

etc in Column A

I need a Data Validation List showing only:
All
Various
Finance
Exploration

Is there a way to get rid of the duplicates?

Thanks!

 

[anubis]

Hello arjanz and Welcome to the Board!

One way is an Advenced Filter.

Data --> Filter --> Advanced Filter --> Check the "Copy to Another Location" --> Check the "Unique Records Only"

Then use data validation from the revised list.

HTH

 

[arjanz]

hi

thank you for your answer but that's not what I'm looking for. The column I'm using for my data validation list is on another spreadsheet and is constantly being edited.

So once Excel 'builds' the dropdown box it should check if there's duplicates in it or something...

thank you!
Arjan

 

[Aladin Akyurek]

Re: hi

thank you for your answer but that's not what I'm looking for. The column I'm using for my data validation list is on another spreadsheet and is constantly being edited.

So once Excel 'builds' the dropdown box it should check if there's duplicates in it or something...

thank you!
Arjan

Book2
	A	B	C	D
1		0	4
2	Items	Idx	D-Items
3	All	1	All
4	All		Various
5	Various	2	Finance
6	Various		Exploration
7	Various
8	Finance	3
9	Exploration	4
10	Exploration
11	Exploration
12	Exploration
13	Exploration
14	Exploration
15
Sheet1

Formulas...

B1 must house a 0.

B3, copied down:

=IF((A3<>"")*ISNUMBER(MATCH(A3,A2:A2,0)),"",LOOKUP(9.99999999999999E+307,$B$1:B2)+1)

C1:

=LOOKUP(9.99999999999999E+307,B:B)

C3, copied down:

=IF(ROW()-ROW(C$3)+1<=$C$1,LOOKUP(ROW()-ROW(C$3)+1,B:B,A:A),"")

Now define ItemList via Insert|Name|Define as referring to:

=Sheet1!$C$3:INDEX($C$3:$C$65536,MATCH("*",$C$3:$C$65536,-1))

and use ItemList as Source in cells you want to data validate.

 

[arjanz]

Thanks! It's working, great!

 

[wolverineb]

I realize this is an older topic, but I have to point out a minor bug in the above solution.

If you copy the B3 formula down past the end of the contiguous list, it will continue to count each blank row as a new "unique" entry. This matters for my data because I need new entries to update the validation list, so I need to have the formulas copied beyond the current list's endpoint.

To fix it, I have changed the

=IF((A3<>"")*ISNUMBER(etc. part of the formula to
=IF((A3="")+ISNUMBER(etc.

Thus if A3 is blank OR it equals the previous entry, B3 will also be blank.

Hope this isn't too nitpicky and I may still be missing something, but it works for me and I wanted to post just in case someone else runs into it.

Thanks,
Dave

 

[Aladin Akyurek]

I realize this is an older topic, but I have to point out a minor bug in the above solution.

If you copy the B3 formula down past the end of the contiguous list, it will continue to count each blank row as a new "unique" entry. This matters for my data because I need new entries to update the validation list, so I need to have the formulas copied beyond the current list's endpoint.

To fix it, I have changed the

=IF((A3<>"")*ISNUMBER(etc. part of the formula to
=IF((A3="")+ISNUMBER(etc.

Thus if A3 is blank OR it equals the previous entry, B3 will also be blank.

Hope this isn't too nitpicky and I may still be missing something, but it works for me and I wanted to post just in case someone else runs into it.

Thanks,
Dave

Another way is just to plug in another IF...

=IF(A3<>"",IF(ISNUMBER(MATCH(A3,A2:A2,0)),"",LOOKUP(9.99999999999999E+307,$B$1:B2)+1),"")

Even better: If you're on Excel 2003, convert the area in A:C into a list by means of Data|List|Create List. The list functionality then takes care of automatically for the copying down the formulas at every new entry.