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
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
