Over the last few years, I've tried to do something in Excel. I optimistically survey the documentation for the function(s) I need, but I always walk away defeated. Can someone tell me how to do the following?

I have a table with two columns:

Data1.........Data2

------------- --------------

red...........0

orange .......1

yellow........0

green.........7

blue..........3

indigo........0

violet........0

I'd like a simple way to derive a single cell, with value:

orange, green, blue

That is, wherever the data in Data2 <> 0, take the corresponding Data1 value and concatenate to the cell's value. The commas should be able to be an arbitrary separator character so one would not be confused by "blue green" versus "blue, green"

Thanks in advance for a solution!

I have a table with two columns:

Data1.........Data2

------------- --------------

red...........0

orange .......1

yellow........0

green.........7

blue..........3

indigo........0

violet........0

I'd like a simple way to derive a single cell, with value:

orange, green, blue

That is, wherever the data in Data2 <> 0, take the corresponding Data1 value and concatenate to the cell's value. The commas should be able to be an arbitrary separator character so one would not be confused by "blue green" versus "blue, green"

Thanks in advance for a solution!

Last edited: