creating a filtered range/named range

[mark kubicki]

I have a range of cells that have a combination of data and null entries,
and would like to use a filtered portion of those cells in the range that
contain data as an input list for a pull-down elsewhere in the workbook

the cells in the range always have (1) of (3) combination of values:
---> "12345_abc..."
---> null
---> "Total_12345_abc..."

I want the pull down list to only show the "12345" portion, and for all of
the entries to be unique

this sequence repeats # times for the length of the range
ex "123_abc", "", "Total_123_abc,
"456_def", "", "Total_456_def,
"789_ghi", "", "Total_789_ghi,
...
so, my pull-down would include: "123", "456", "789"...



....any suggestion on where to start?
thanks in advance,
mark

 

[Mike Fogleman]

The exact composition of your data list is a little vague. I am assuming it
looks like example 2 which appears like a comma delimited text where the
entire contents of one row is in the first cell. You can use a formula to
extract the leading numbers from the text up to the first underscore ("_").
Drag this formula down beside your list to create a new list:
=MID(D4,2,FIND("_",D4)-2)
Change D4 to the first cell in your list before you drag down the formula.
Now you need to create a Unique list from your new list. This is done with
Advanced Filter found under Data...Filter menu. Advanced filter requires a
header to work, so enter some word in the cell above your new list. Now open
the Advanced Filter and select Copy to another location, enter a cell from
the same sheet in the Copy to: box, select Unique records only and click OK.
This final list is what your drop-down will use.

Mike F