VBA output Data Validation List, or create function to remove duplicates from list

ajamess

Board Regular
Joined
Sep 13, 2016
Messages
92
I am working on a project where I need to have data validation dropdowns that drill down from a top category. Example below.

FruitsApplesRed
FruitsApplesGreen
FruitsBerriesRaspberry
Fruits
BerriesBlackberries
FruitsBerriesStrawberries
VeggiesBeansGreen Beans
VeggesBeansBaked Beans
VeggiesBeansRed Beans
VeggiesCornWhole Kernal
VeggiesCornCreamed

<tbody>
</tbody>

This example is extremely simplified This would be several thousand rows long with 7 columns instead of 3, however you get the idea. These columns will change regularly, so I don't think it would be convenient to use this with a column for each selection using indirect.

I was hoping to use this method:

http://www.contextures.com/xlDataVal13.html

I can use this with a helper key column, and selections 1 and 3 in the example would come out well, however the dropdown in number 2 would have duplicates. For example for fruits it would show: "Apples, Apples, Berries, Berries, Berries" With how many rows there are in real dataset this is an issue.

I can't make make a space on another sheet where I remove duplicates and use this new file from the range. Because the final resultant dropdowns will be copy'd down ~ 100 rows. So for example, the final dropdowns from the example would be in a1,b1,c1 (for the 3 categories, and then would copy these to use down below) So I'd like a formula in the drop down that if a1 selects fruits, b1 has the fruits options. And then in a2 I put veggies, b2 has the veggies options.

I figure I have two options for approaching this problem using a created function, but I'm not sure which (or either) are possible.

1) Output a list from a VBA Function:
My thought is to create a function that I would enter the offset range into this function and it would output a list of individual outputs in that range (Let's say for Fruits). It seems data validation won't take an array, (which would have been easy). I'm not sure if its able to output a list of strings, in a way that data validation could accept however. Ex: "Apples","Berries"

2) Output a selective range from VBA function:
The thought here would be if I input the offset range and it would pick the cell locations out of that range that are unique. For example, the offset formula for fruits would send "B1:B5" into the function. And the function would return "b1","b3" to give a range to data validation.


Hopefully I've expressed problem clearly (I tried my best, still fairly new at this). If not please let me know any questions.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,718
Members
415,922
Latest member
gemmatay88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top