Automatically Extract Unique Values from cells with comma delineation

[Mydako]

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.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Colour[/TD]
[/TR]
[TR]
[TD]Teapot[/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]Towel[/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD]Phonestand[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Toiletbrush [/TD]
[TD]Black, White[/TD]
[/TR]
[TR]
[TD]Vase[/TD]
[TD]Green[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[sandy666]

try PowerQuery
split colours by comma and space (or any other delimiter) then group colours with count distinct

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Product[/td][td=bgcolor:#5B9BD5]Colour[/td][td][/td][td=bgcolor:#70AD47]Colour[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Teapot[/td][td=bgcolor:#DDEBF7]Black[/td][td][/td][td=bgcolor:#E2EFDA]Black[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Towel[/td][td]White[/td][td][/td][td]White[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Phonestand[/td][td=bgcolor:#DDEBF7]Blue[/td][td][/td][td=bgcolor:#E2EFDA]Blue[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Toiletbrush[/td][td]Black, White[/td][td][/td][td]Green[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Vase[/td][td=bgcolor:#DDEBF7]Green[/td][td][/td][td][/td][/tr]
[/table]

 

[Mydako]

Thanks for the quick response Sandy. Unfortunately (and I usually remember to mention this) this is for work, and our paranoid IT security doesn't allow VBA, add-ons, plug-ins or the like, and thus far they have not been favourable to the idea of exceptions. So PowerQuery might work, but it won't work here sadly. Thank you though.

 

[sandy666]

tell them about upgrade 2013 to 2016 or higher and PowerQuery will be built-in
have a nice day

 

[Mydako]

Ha, trust me, I've been begging for that for over a year.

 

[sandy666]

I just have to be sorry

 

[James006]

Hello,

Say in cell B5 you have three colors : Black, White, Red

As an initial step, you could have in cell C5 the following Array formula:

=MID(","&$B5&",",SMALL(IF(ISERROR(SEARCH(MID(","&$B5&",",ROW(1:99),1),",")),"",ROW(1:99)+1),COLUMN(A4)),SMALL(IF(ISERROR(SEARCH(MID(","&$B5&",",ROW(1:99),1),",")),"",ROW(1:99)),COLUMN(A4)+1)-SMALL(IF(ISERROR(SEARCH(MID(","&$B5&",",ROW(1:99),1),",")),"",ROW(1:99)),COLUMN(A4))-1)

This formula can then be copied to Columns D and E ...

Hope this will help

 

[Mydako]

Thanks James. That seems to work, I can use this to separate the colours and then collect from a different field. It's steps more than PowerQuery, but if it works, it works. Thanks.

 

[James006]

You are welcome ...

If we were to assume your concatenated cells are located in column B ...

and the split into individual cells ... say in C2:E6

you could extract the Unique colors with following Array formula

=INDIRECT(TEXT(MIN(IF(($C$2:$E$6<>"")*(COUNTIF($G$1:G1,$C$2:$E$6)=0),ROW($2:$6)*100+COLUMN($C:$E),7^8)),"R0C00"),)&""

Hope this will help

 

[Mydako]

Thanks James. That's more or less what I ended up working out, but more concise than what I came up with.