Dropdown to list unique values from growing list?

[Ymir20000000]

Hi.

I have a single Excel spreadsheet on which I have an ever-growing list of names in one column. (It'll never be longer than a few hundred rows.)

Is it possible to create a dynamic dropdown box, on that same page, that will list ONLY the UNIQUE values from within that growing list?

For example, the list may contain:

PCT (Patient Care Technician)
Asset Protection Specialist
Housekeeper
PCT (Patient Care Technician)
Asset Protection Specialist
Asset Protection Specialist
Dimentional Trainee
Finisher Trainee
PCT (Patient Care Technician)
PCT (Patient Care Technician)
Housekeeper
[etc]

I want the dropdown to only show the unique values from that growing list:

Asset Protection Specialist
Housekeeper
PCT (Patient Care Technician)
Dimentional Trainee
Finisher Trainee

It's a boon if I can organize the list alphabetically, too. But not a problem is not possible.

Thank you in advance for your help in this.

Please keep in mind I am pretty much a novice at this.

Thanks.

--John

[Glenn Kennedy]

1. Set up a named range on your original column to expand as needed, using this formula:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$1000,COUNTA(Sheet1!$A$2:$A$1000))

Hit CTRL-F3 to view how the Named range (called "list") is set up.

2. Then use this array formula, in D2 copied down:

=IFERROR(INDEX(List, MATCH(MIN(IF(COUNTIF($D$1:D1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0)),"")


3. Then set up another named range ("dropdown"), using this formula:

=OFFSET(Sheet1!$D$2,,,SUMPRODUCT(--(LEN(Sheet1!$D$2:$D$100)>0)))


4. Then use Data validation In H2 to h20: DATA/DATA VALIDATION/List and =Dropdown in the "applies to" box.


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...


uniques and sorted.

NB The intermediate column (D, in yellow) can be located on a hidden sheet, or wherever, if you want.

[MarvinP]

Hi John,

I'd do this problem a little different than Glenn. I have problems with those CSE formulas so I'd use a Pivot Table and a little VBA to refresh the pivot and sort. See the attached that keeps Glenn's Dynamic Named Range of "List" and does a Pivot to get your answer.

PT from DNR with Refresh Event and autosort.xlsm

[Glenn Kennedy]

Fair point, although I am no longer a fan of Pivot Tables... How do you go from there to a dropdown (the second part of the q - which I failed to spot when I first replied...)?

basically - how can you make a DV "list" or whatever grow/shrink with the Pivot table?

[MarvinP]

Hey Glenn,

I'm so glad you asked... Create a Dynamic Named Range using the Pivot Table column. In the Validation List use the DNR and poof, it works.

[Glenn Kennedy]

Yeee Haaa!

perfecto.