Extract UNIQUE Items for Dynamic Data Validation Drop Down List
Why Stack Overflow recommends it
Stack Overflow users link it when they need a dynamic drop-down or a unique list built from repeating values using classic Excel formulas.
Recommended by 2 people across 2 different months, 2019 to 2021.
What it is
An Excel tutorial by Leila Gharani (Xelplus) showing how to build a unique-items list with INDEX, MATCH and a reversed COUNTIF, without Ctrl+Shift+Enter. She then uses OFFSET with COUNTIF to make a data validation drop-down that grows and skips empty cells.
What people said
Recommends JvdV · 2019-04 · Stack Overflow · on “How can I perform a drop down list based on multiple Vlookup outcomes?”
I highly suggest you should watch both this [this video] video on a drop-down list for unique values, and it's follow-up [LINK] on a dependent drop-down list. Follow these steps and it won't be too hard. Good luck.
Recommends MmVv · 2020-03 · Stack Overflow · on “Excel Help: Dynamic range + Data Validation”
After some days of searching and trying I got what I wanted - wasnt wasy job at all. Needed to combine more functions with the help of couple of videos from Leyla (Xelplus): [LINK] [this video] First step was to make unique list of my values (text in my case) on separate sheet: =IFERROR(INDEX(t_VarGroup[Vargrouptext];MATCH(0;INDEX(COUNTIF($J$2:J2;t_VarGroup[Vargrouptext]););0));"") Then I needed to "extract" all the values that are belonging to the certain unique values: =@IF($I3<COLUMNS($K$2:K$2);"";INDEX(t_EDS[[VarEDS]:[VarEDS]];AGGREGATE(15;3;(t_VarGroup[[Vargrouptext]:[Vargrouptext]]=$J3)/(t_VarGroup[[Vargrouptext]:[Vargrouptext]]=$J3)*(ROW(t_VarGroup[Vargrouptext])-ROW(t_VarGroup[[#Headers];
Cites David Wooley - AST · 2020-07 · Stack Overflow · on “How to add unique serial numbers using VBA code”
By Hook or By Crook I got it working. Got what you wanted , and did so without dictionary's, without using Arrays, dictionaries or keys (which would be a better way I feel but too much headache for me right now). Must understand I am smashed, the area I live in is driven me insane and need a holiday. it was done the "cheat" way (by VBA worksheetFormulas - which is no cheat but just what I think) and not the Arrays/Dictionaries I was looking at Here [LINK] , Here [LINK] and Here [LINK] , its less generic and less stable I feel, so you must clear column A each time you run otherwise some the id's will +1 each time, and if you add a value in between others, the id's will change. So really this
Cites user15233471 · 2021-04 · Stack Overflow · on “INDEX MATCH formula instead showing result show zeros”
I saw some tutorials [this video] to make the unique list of names dynamically in Excel. but when I applied this formula to my data I got zero instead of the result value. I tested this formula on sample data but Still get zeros. the formula is : =IFERROR(INDEX(Table2[names],MATCH(0,INDEX(COUNTIF($E$5:$E$13,Table2[names]),),0)),””) the sample table is [LINK] when I press the F9 button on the formula box I see the correct result [LINK] also, I changed the formula to this one but there is no change in the result. =IFERROR(INDEX(Table2[names],MATCH(0,INDEX(COUNTIF($E$4:$E5,Table2[names]),),0)),””) the difference is in the $E$4:$E5 UPDATE : thanks to @Rory for answering. the problem was in a circular