Drop down list that excludes duplicates 4

[LeighAnne]

I want to use Data Validation to create a drop down list based on a list I have in a sheet. However, the list includes duplicates, and I only want the drop down to show each of the items once. Any ideas?

LIST EXAMPLE:
Fox
Dog
Cat
Dog
Rat
Fox

DROP DOWN TO CONTAIN:
Fox
Dog
Cat
Rat

 

[Loomah]

Filter your list using Advanced Filter and select Unique Values only.
Filter the list to a new location and use the new list for your dropdown

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[LeighAnne]

Thanks Loomah!

Much appreciated!

 

[LeighAnne]

One further step, the original list can be added to by the user. How can I get the advanced filter to update when the list changes?

 

[xlbo]

Can't without code or manual update

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

LeighAnne,

if you want a list of unique values from an updateable list, that updates automatically without code or user intervention, then you could create the list by formulae.

For example, let's assume your list is in cells A2:A7, then in column B have this formula copied down:
=COUNTIF($A$2:A2,A2)
copied down further than the list in column A, in fact down as far as the maximum possible entries could exist in column A.

Then in column C have this formula:
=IF(A2<>"",IF(B2=1,COUNTIF($B$2:B2,1),0),0)
copied down as far as the formulae in column B.

Then in column D fill in the series 1 onwards - to do this type a 1 in cell D2 and right click the fill-handle ( the black cross that appears when the mouse is over the bottom right corner of the active cell ), and drag down as far as necessary, and when you let go of the mouse button a shortcut menu will ask what you want to do .... choose Fill Series.

Then in column E have this formula:
=INDEX($A$2:$A$17,MATCH(D2,$C$2:$C$17,0))
copied down as far as all the other formulae ( replace the 17 with the row number of your last formula ).

You will need a defined name to refer to your list, and you create that by doing Insert/Name/Define and choosing a name ( such as MyDropList ) and typing this formula in the Refers To box:
=OFFSET(Sheet1!$E$2,0,0,MAX(Sheet1!$C$2:$C$17),1)
replacing the 17 with the row number of the last formula in the list.

Sounds longwinded, but this method automates everything without having to rely on code, or forcing the user to take any manual action.




Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

are you sure you're condoning copying formulae down to row 65536 Glenn ??

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

LOL, no. I usually copy down a couple of hundred of rows, well over what the users tell me is the most likely maximum number of entries ( e.g. "No one will ever go over 40 entries, I promise. ").



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

I guess I've just been oversensitized to this - we've just bought 2 companies in 2 years so all those "I promise it won't go over X" have been broken at least twice now ;-)



Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[LeighAnne]

Thanks GlennUK!

That worked a treat!

Regards,

Le

 

[GlennUK]

My pleasure



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.