Creating an named range from a column of text entries

**Matt6037** · 10-03-2015, 02:40 AM

Hello,

I have an expanding column of text data that I need excel to select particular items from (based on a text entry in an adjacent column) and collect them in to a named range (that can subsequently be used as a drop down list). I also want excel to automatically remove duplicates and order the named range alphabetically.

Example:

Rose	Liquids
Cedarwood	Liquids
10ml Round	Bottles
Rose	Liquids
Syringe	Equiptment
Sandalwood	Liquids
Coconut Oil	Oils
20ml Square	Bottles
Ambrette	Liquids
50ml Beaker	Equiptment

To Clarify.... From The above set of data, I need to extract 4 named ranges (Liquids, Oils, Bottles & Equiptment).

The named range "Liquids" for example will need to return a result of {Ambrette, Cedarwood, Rose, Sandalwood} (duplicate "Rose" removed and all alphabetically ordered).

I also need to ensure that as the data set grows excel is able to update and expand all four of the named ranges.

Any help would be greatly appreciated.
Matt.

**teylyn** · 10-03-2015, 03:16 AM

Hello,

this seems to be backwards. Create dynamic ranges in four columns for the four groups and add new items into each column as required.

The data table you show above can then refer to these named ranges.

It may be possible to use formulas to sort your data and generate complex formulas that create range names, but it will be way easier to just type them in four separate columns.

Why not go for an easy solution that you will be able to maintain on your own.

cheers, teylyn