Transposing multiple columns

[chickenlicken]

I am wondering if there is a more efficient way of doing this than the approach I'm takign

I have a workflow where values that I want to consolidate into a single column are spread across multiple - for example in the format Fruit (1), Fruit (2), Fruit (3) - I would want these to appear under one column labelled Fruit.

Is there a way to do this using a macro that looks for similar column names and transposes these one at at a time, or is the best solution simply to transpose one at a time?  The columns might change in future and it would be good to be able to have  workflow that doesn't have to be updated manually.

The extra complication is that for e.g. Fruit (1), Fruit (2) there is an extra variable, Fruit type (1), Fruit type (2) which needs to be associated with the Fruit column

I'm stuck!  Any suggestions?

[JosephSerpis · Accepted solution]

Hi @chickenlicken I mocked up a workflow let me know what you think?

[Maskell_Rascal · Accepted solution]

Hey @chickenlicken 

The attached should work. Transpose the data, use Cleanse tool to get rid of numbers and punctuation, add a ranker, and then crosstabs everything back grouping by ID and Rank. 

Let me know if this works for you. 

Thanks!

Phil

[chickenlicken]

Brilliant does just what I'm looking for, thank you

[chickenlicken]

Could accept either of these as solutions!