Parse Attributes with Proper Output Using Commas and 'And'

[TR]

Using the table below my goal is to have a properly formatted string I can use for reporting purposes. I would filter based on whether the column attributes included "Improve" and concatenate them. However, I'm not too sure how to go about formatting the Result properly by removing additional "Improve"'s and using commas and the word 'and' when appropriate. Thanks in advance for suggestions!

ID	ProdDessert	ProdSupplies	ProdFood	ProdFoodSvc	Result
1	Improve Dessert Sales	Ok Supplies Sales	High Food Sales	Ok Food Services Sales	Improve Dessert Sales
2	Ok Dessert Sales	Ok Supplies Sales	Improve Food Sales	Ok Food Services Sales	Improve Food Sales
3	Ok Dessert Sales	Ok Supplies Sales	Ok Food Sales	Ok Food Services Sales
4	Ok Dessert Sales	Ok Supplies Sales	Improve Food Sales	Improve Food Services Sales	Improve Food Sales and Services Sales
5	Improve Dessert Sales	Improve Supplies Sales	Ok Food Sales	Improve Food Services Sales	Improve Dessert Sales, Supplies Sales and Food Services Sales

[MarqueeCrew · Accepted solution]

My iPhone Suggestions:

1. TRANSPOSE - key is ID, data is all fields
2. FILTER - contains([value],"improve")
3. SUMMARIZE - groupby ID concatenate value and set delimiter to " & "
4. FORMULA - 

   ReverseString(Replacefirst(Reversestring(value),"&","dna"))

[JohnJPS]

More mobile phone thoughts: if we had a Formula PlugIn "ConcatenateIfContains", that would allow this to be one formula tool.

[TR]

That is so awesomely close and by iPhone, I wish I could give 2 stars! I tweaked it a bit to get rid of the extra "Improve"'s and added spacing. Thanks!

ReverseString(ReplaceFirst(ReverseString([Concat_Value]), "evorpmI&", "dna "))

[MarqueeCrew]

Thanks for the kudos. Maybe you'll be iphoning in solutions to others soon too. 

If you are at inspire, feel free to say hi. 

Cheers,

mark

[TR]

Thanks, I'll be there and will definitely look to say hi! 

I did have to do some extra tweaking on the workflow as I noticed some minor details that just needed a few more formulas and steps to add the records filtered out without "Improve". It's an ugly workflow but it works, I accomplished what I needed and thanks for getting me there.

Best,

Todd

[jdunkerley79]

Formula AddIn sounds a good idea... :)