Merging columns with suffixes into a column without

Paul_s_Moody

Hi,

I am combining data from a pension scheme and am hoping there is a way to avoid "manually" typing lots of data into a formula tool.

In the data set many of the columns need to be combined into a single column as they have the same features, but some times there is only 1 instance of the feature.

I've used a dynamic rename tool to change the input column name into a feature column name, but I would like to be able to combine the feature columns easily.

As an example a subset of the column names are:

Feature_A_1

Feature_A_2

Feature_B

Feature_C_1

Feature_C_2

Feature_C_3

I can solve this by typing into a Formula tool (adding new columns)

Feature_A = [Feature_A_1] + [Feature_A_2]

Feature_C = [Feature_C_1] + [Feature_C_2] + [Feature_C_3]

and then using a select tool to deselect the _1, _2 and _3 columns from the data.

I have more than 15 types of "Feature" and in some cases suffixes from _1 to _10.

Is there an easy way to to this? Or do I need to just type (well copy/paste out of Excel) row by row into the formula tool?

Thanks in advance

binu_acs

@Paul_s_Moody this can be achieved with the help of the transpose/cross tab tools. I am attaching a sample workflow for your reference. 

Transpose_200223.yxmd