Concatenate and recorded into a new Worksheet/Workbook

Question

I have the following data

IDAddAddAddAddRemoveRemoveRemoveRemoveRemovex123BananaAppleOrangeCarrotsGrapes    x234BananaApple

Grapes Coconut    x131 AppleOrangeCarrotsGrapes

etc.

I want to concatenate ID and all the Add columns and have it recorded in another worksheet/book

And same thing for the Remove column

So the final output looks something like:

AddRemovex123-Bananax123-Grapesx123-Applex234-Coconutx123-Orange

x131-Grapesx123-Carrots x234-Banana x234-Apple x234-Grapes

etc.

The Add and Remove columns could be more than 4. Basically I want to concatenate all the Add column and record them on a different worksheet (Column A). And all the Remove column recorded in same worksheet (Column B) .

Qiu · Accepted Answer

@zealisk

This is an interesting one. we can use Transpose and CrossTab.