Excel Drop-down - Data Validation with no duplicates

/

Want a drop-down to show no duplicates? And automatically update?

For example you have a table that is updated weekly, which shows weekly employee pay.
You want a drop down based off the name column for a dashboard, but if you use data validation to do it, you get duplicates due to the employee name being there for every week,

Drop-down Validation.png

Hard coding the names doesn’t work either, as new employees are often hired. Plus it would be a manual process - and if you wanted no blanks in the drop down you would have to extend the data validation to cover new names.

data validation drop-down.png

Filter Function to the rescue

As of writing this mid 2020, you do need a recent version of Excel for this to work. As we are utilising the new array mechanic. If you have the =FILTER() formula in your Excel, you are good to go.

This has existed in Google Sheets for years, but has now arrived in Excel.

The formula we want, which we can add to F3 (delete names currently there), is:

=UNIQUE(FILTER($C:$C,($C:$C<>"")*($C:$C<>"Names"),""))

It’s quite simple. You filter by starting with the data you want returned (much like Index), then you add in the criteria to filter by. Since I am filtering everything in column A, I tell it to ignore blanks AND ignore the column name. It uses boolean logic (which means you would use + instead of * if you wanted OR).

Finally, it is wrapped in Unique to get rid of duplicates!

Unique Filter Array Excel.png

The only flaw with this system, is that you can’t impute this directly into the validation. You still have select a fixed range, which means you have blanks.

Let me know if you find a way around this!

New - Drop Down With No Blanks

We can now use arrays in drop downs!

Have a helper column with your filtered list. Go to data validation, instead of selecting the range just click on the cell which has the unique/filter formula (F3 in example above). All you need to do is add a hash to the end, so $F$3#

Click okay and you now have a drop down with no blanks!

Searchable Drop Down

=FILTER( UNIQUE(SAPItems[CATEGORY]), ISNUMBER(SEARCH(H3,UNIQUE(SAPItems[CATEGORY]))),"")

Create a helper column, and copy in the above formula. Change the bold items to be your data range - it can be unique or filter, or both.

In H3, create a drop down using the method above with the hash. Disable the error message in the validation.

Now the magic, you can write on H3 and the drop down will search for what you have written! For example if you write “E” the drop down will contain all the items with the letter E.