Data validation - list

[Tiina]

Hi,
I'm setting up a Data Validation cell using 'List". However, my source data has each option on the list more than once; therefore my drop-down list contains each of the options more than once. Is there a way to make this drop-down list have each option available just once. I know I can create a separate 'spot' for my list options and 'remove duplicates... but I'd like to avoid that if I can.
Thank you!

 

[DRSteele]

Well, we can create a formula that lists the unique items from the list that contains duplicates, and then use that new list as the source for the data validation dropdown. Would that suffice?

 

[Tiina]

Yes, I think that would be fine. Any suggestions for doing the formula?

 

[DRSteele]

Yes. Watch this video on ExcelIsFun youtube channel because it is exactly what's needed for a formulaic solution. You can go to the website suggested in the video and download the sample file so you don't have to re-create the wheel. https://www.youtube.com/watch?v=3u8VHTvSNE4

Alternatively, we can use a PivotTable to create the Unique List, as suggested here at Excel University: Unique Data Validation Drop-Down From Duplicate Table Data | Excel University

 

[Tiina]

OMG my head is spinning. Made me realize how little I know about Excel... or alternately: how much I still have to learn to be an expert!

I think this will work for basics. It will not sort the values to be in alphabetic order, correct? He is referring to a different formula to use for that. Is that available somewhere online as well?

 

[DRSteele]

Sorting them will require a lot more head-spinning. One way around having to use the required complex array formula that uses function MMULT is to use Lenning's PivotTable method at Excel University: PivotTables can be sorted automatically.

In the interim, go to this website, sign up with a valid email address, and vote for the creation of this Excel functionality that contemplates Unique Items Only. Apparently GoogleSheets does this as a matter of policy in drop-down lists and I see no reason why Excel should be limited in such a way. https://excel.uservoice.com/forums/...uld-use-a-data-validation-list-option-that-co

 

[Tiina]

I'll send our staff to the website to vote!!

Meanwhile, I'd like to use the MMULT formula if possible. My head can spin, but I believe I can keep it on straight long enough to get through the formula writing part . However, I'm handing off this spreadsheet to people with very little Excel skills. They can manage with a pivot table but the fewer steps, the better.

One question, though, before we proceed. My spreadsheet has about 3,000 line items and probably about 1,000 unique values. Do you think MMULT formula combined with the one from the video would slow things down and/or make the file very large?

 

[DRSteele]

Stay tuned --- I'm trying to chase it all down.

Interestingly, here is what looks like Girvin's MrExcel post that is the source of his video. http://www.mrexcel.com/forum/excel-questions/488149-unique-list-use-validation-list.html

 

[Tiina]

The guy in the video references Dominic from Mr. Excel. I believe I have seen his name here before. It all begins and ends with Mr. Excel!

 

[Tiina]

Pivot table basis will work for me! I figure I'll just do a pivot and write a macro for them to update it (e.g. do a little box for them to click a macro that updates the pivot).

Thank you for all your help and for pointing me to the youtube channel for excel tips. It's 7pm on Wendesday and I'm still geeking out looking at formulas LOL