Automatically Extract Unique Values from cells with comma delineation

Mydako

New Member
Joined
Apr 19, 2018
Messages
26
Hello all,

I'm working on a database, and I have it set up to automatically pull unique values from the one column. I use these unique values to populate a dropdown menu so I can sort the entries easily.

The formula I have works for most things, but occasionally I'll have a cell that needs more than one value, unfortunately when I use the formula to draw out unique values it pulls out everything in a cell as one.

For example I'm making a database for what colour a specific product comes in. 95% of the products are only available in one colour, but some come in two or three.


ProductColour
TeapotBlack
TowelWhite
PhonestandBlue
Toiletbrush Black, White
VaseGreen

<tbody>
</tbody>

Currently this is the formula I'm using

=IFERROR(INDEX(Name,MATCH(0,COUNTIF(Name,"<"&Name)-SUM(COUNTIF(Name,"="&A$1:A1)),0)),"")

Name is a named range for the colours.

The above formula would return

Black
Black, White
Blue
Green
White

It groups the black and white options together, so when you check the drop down it has the option of "Black, White." Now with the way I have the drop down working black or white will call up the black or white item, and the "black, white" one, so that's fine, it just looks clunky. As more and more products are in more than one colour though this list will get cluttered with every combination being its own thing.

What I'd like to return instead of the above result is:

Black
Blue
Green
White

I'd want "black, white" to be understand as black or white, not a unique colour of "black, white"

Is there a way to do this easily? (We only have maybe 100 colours available, so it wouldn't be /horrid/ to write them into a unique value list manually, but I'd like to avoid that) Also I'm just using comma separation as an example, if there is another symbol that would work that's fine. I'm on Excel 2013 if that makes a difference.

Googling I can find lots of advice on putting multiple values into a single cell, but nothing on extracting them.

Thanks muchly
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Watch MrExcel Video

Forum statistics

Threads
1,127,481
Messages
5,625,015
Members
416,065
Latest member
meiravmeron

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