Extract UNIQUE Items for Dynamic Data Validation Drop Down List

Anybody ever have the problem of this formula returning a column of 0's? Is there a setting I'm missing Excel v2016

That was a great explanation, thank you. But how du you continue with the same function further down Row D?

Simply genius. Anything you don't know in Excel?

Great Video!
To get a sorted unique list, you can use the followings:
IFERROR(INDEX(TableDiv[Division],MATCH(0,INDEX(COUNTIF(TableDiv[Division],"<"&TableDiv[Division])-SUMPRODUCT(COUNTIF($D$7:D7,TableDiv[Division])),),0)),"")

Easiest way : You can copy unique values using advanced filter and then creating a list with data validation technique.

awesome

Thank you. it saved my day. Helped me in completing a project.👍

Please explain if value greatet than 12hours but less than 24 hours count only unique items

usually i understand all your videos.. but today...(add my english)... i didn't :D

You are a life saver, Leila! Tks!

So good

How about using a Pivot Table to save us from all the complex functions?

the array nature of countif is the unlocker here!! awesome Leila! Thanks!

Brain imploded

This is extremely helpful! I want to create a dropdown list using this technique, but also add one additional value that is not found in the unique list. Haven't found a way to do that yet..

Excellent video, ingenious formulas. However, with Office 365 (now Microsoft 365), this whole lot is now much simpler: we could use the new UNIQUE function - right? And while we are at it, we can also do SORT(UNIQUE(<range>)). My only issue is that when I do it inside a drop-down list (data validation), Excel does not like my table references (e.g., TableDiv[Division]). Putting them under Name Manager or INDIRECT (with quotes) didn't work either. Can anyone help?

Many questions in one class...thank you mam..

You are beautiful 😍

Hello, how can I now add the App column now to a dropdown? As such that you create a dependent dropdown? So you have one dropdown for division and the other for the app which are conected to each other.

Can we use data validation in another sheet?

Your voice is so annoying

Great video! Worked well for me, until I DELETED an item from the original list. I'd love some idea's on how to make this more robust?

leila, try directly to jump to the point.. you talk a lot... instead of talking what cannot be used, directly jump to what can be worked.

Amazing and helpful video. Would you able to apply this if you are indexing unique values if two or more criteria is met?

Hi
Just watched this Video - quite like your approach. I do have a different scenario I would like to submit.
I have an application where I need 5 different languages. And I have basically all my formula referring to a LANGVAL = to say EN or FR or SP etc.
In your example above, I set up division to be EN or FR with the APP side showing the values that I require. I am trying to get the APP values to display
in the lookup drop-down fields based on the language requested.
Any ideas on how to make that work? Thanks Pierre

Best explanation of how to use OFFSET for data validation I've seen yet, well done. I've combined this with UNIQUE and FILTER for a cascading drop down based on powerquery tables. I always forget exactly how to do it and have to look it up and this is the best one I've found so far. Named Ranges help alot too. Good stuff.

You are genuis! And thanks btw)))

I don't know the explanation is a bit confusing to me.

How much easier will this get if I need this, but the data is on another sheet lol Asking for a friend lol

Laila. I watch your videos with full attention but these sudden adds breaks it up. Thanks for sharing so nice videos. I hope you also give lectures on data science too.

Brilliance, you are the best!! Love it

Thankyou so much for your vidio, its help me

The formula is brilliant. Just need to know how to avoid blank fields using the same formula?

Hi Leila, Love your videos! Is there a way to pull unique lists within the data validation list without having to use a separate table from columns A and or B? I tried using the filter formula and the unique formula but data validation lists do not like those formulas.

I was able to replicate and get successful results, however, my referenced data table needs to be sorted by the specific column in order to provide the results as expected.

Gd mrng leila

You could use today a combination of Unique, filter

Can this be done with numbers? Like 6.3215 ? (Random numbers separated with a decimal point)

Hey there! This video does EXACTLY what I've spent DAYS trying to figure out how to do, but when I put the formula in the Data Validation box, it gives me an error telling me there's a problem with my formula. But, the formula works perfectly outside of Data Validation. What am I doing wrong?

what is the difference between unicode 45 and 8208

Leila, thank you for your directions
Your formulas are sexy

Mam how do sabtotal unique value count in excel filter .

My "divisions" table includes blank cells, so when I listed all the unique "divisions", it recognized the blank cells as unique "0". Has anyone tried this and fixed it? TIA

Mind blown, amazing.

Thanks Leila for all your wonderfull and informative videos, I learned a lot and with that I found an even more easy approach:
Format your source as a table
Put the following formula on a separate tab or hidden in cell A1 =SORT(UNIQUE(FILTER(Table2[Cities];Table2[Cities]<>""))) this formula returns a sorted list of unique cities without blanks from a table where it looks at the column Cities.
Reference in the data validation list to cel A1#

Why didn't use a unique formal

Awesome Leila.. You are a genius..

I am having zeros "0" how do I remove them?

thank you for your sharing. I have a question is if I want the dropdown list to locate in another sheet, how can I do?