Hi Chris welcome to the forums. You're on the right track in your second solution. In this case it is much better to reshape your data. This is such a common need that Tableau has an unsupported Excel plugin that does the work for you: http://kb.tableausoftware.com/articles/knowledgebase/addin-reshaping-data-excel
This will create all the necessary rows you'll need to get a single color on each row of the new color column. But before you use the reshaper I suggest you do a find/replace on all those blanks so you get N in all the blanks:
Fruit Green Red Yellow Cherry N Y N Banana N N Y Lime Y N N Apple Y Y N
After you run this through the reshaper it'll look like this:
Fruit Col2 Col3 Cherry Green N Cherry Red Y Cherry Yellow N Banana Green N Banana Red N Banana Yellow Y Lime Green Y Lime Red N Lime Yellow N Apple Green Y Apple Red Y Apple Yellow N
Now you can basically delete Col3
[Edit: Driving to Home Depot for the 4th and final (I hope, I hope) to finally get the swamp cooler going. I realized how dumb this last statement was. You need that Col3 to use as a filter. Because of this NOT filling out the data with Ns is probably your best option. See below.]
I understand your data is probably more complex than this, so feel free to ask more questions if you get hung up at some point.
Hope this helps,
EDIT: The find/replace step may not be necessary, unless it's important to get an equal number of rows for each fruit. Without adding in the Ns here's what the result would look like:
Fruit Col2 Col3 Cherry Red Y Banana Yellow Y Lime Green Y Apple Green Y Apple Red Y
Fewer records, but an uneven distribution, which might affect other data calcs down the road.
Message was edited by: Shawn Wallwork
Thanks, Shawn. I hoped to avoid reshaping the data, but it sounds like that plugin should make it much easier.