Reverse Data in Columns

[tangoland]

Hope I can explain this well. I have a list of search words in column A and in column B is the products that address that particular search word and the products are separated by a comma in column B. Each product is listed multiple times under various search words.

What I am hoping to do is reverse this so that the product is listed in column A and the related search words are listed under that product in column B.

Any suggestions?

Thanks,

Tangoland

[shg]

Welcome to the forum.

Post a workbook and show what you have and what you want.

[tangoland]

Sample A symptoms B remedies.xls

here is the attachment of a sample of what i am looking for help with.

column A has symptoms and column B lists the remedies for that symptom.

what i need to do is reverse this making the remedy the main list and the corresponding symptoms listed after each remedy.

[martindwilson]

best i can do is to split remedies by using test to columns
copy paste those columns one below the other repeat for symptoms then run a pivot table report on result sheet 1
or
use text to columns and just run pivot on result see sheet 2 this also shows that you have duplicates in list e.g.
Big/Small Elixer features 2x infor hunger
sheet 2

[JBeaucaire]

This utilizes a String Concatenation UDF, so macros will have to be enabled for this to stay updated on its own. also, you have some typos in your upper section that should be fixed up.

Anyway. With the UDF installed, you can use a the =StringConcat() formula to analyze your primary list above and it will create the second table for you.

It's an array formula, so if you make edits to it, press CTRL-SHIFT-ENTER to confirm the changes, not just ENTER. Just pressing ENTER will cause the braces { } to disappear and the array will break. If that happens, press F2, then CTRL-SHIFT-ENTER to reactivate it. Then you can copy the newly edited formula down the rest of the column.

By the way, I love RPG games... what game is this from?

[tangoland]

Thanks to all the support and...

Big shout out to JBeaucaire for helping me out!!