Adding Multiple Fields based on name similarities

[djr1286]

Hi all,

I have data set up as below. Basically, I want to add the numbers in field A1 and B1, A2 and B2, etc to create C1, C2, etc. I know I can use a formula tool and manually create each C value one by one, but my table goes up to A80 and B80, so a simpler way would be a great help.

Name	A1	A2	A3	A4	B1	B2	B3	B4
John	1	1	1	1	1	1	1	1
Jane	1	1	1	1	1	1	1	1
Jack	1	1	1	1	1	1	1	1
Jill	1	1	1	1	1	1	1	1

[ChrisTX]

Are your field names actually structured as one letter followed by a number?  It matters.

Can you post sample data with actual field names?

[djr1286]

The fields names are not that simple, but they have a similar format.

Name	XX_XXX_YR_MO1_AM	XX_XXX_YR_MO2_AM	XX_XXX_YR_MO3_AM	YY_YYY_YR_MO1_AM	YY_YYY_YR_MO2_AM	YY_YYY_YR_MO3_AM
John	1	1	1	1	1	1
Jane	1	1	1	1	1	1
Jack	1	1	1	1	1	1
Jill	1	1	1	1	1	1

[ChrisTX]

Again: Can you post sample data with actual field names?

Why it matters:  If you tell us a field name is "XX_XXX_YR_MO1_AM": we are guessing what the actual field name looks like

we may use a tool like RegEX to parse the field names.  but we don't know what the actual field names are

If we give you a RegEx based on "XX_XXX_YR_MO1_AM", are you going to be able to figure out how to change the RegEx to match your actual field names?

[OTrieger]

@djr1286 

You can use Transpose tool. Then for the Name field use Regex Replace formula removing digits, so you will stay with the same names.
Next use Summarize tool to Group on Name and sum the value.
With Formula tool use Replace _M_ to _M03_ . Then you will need to use the Cross Tab and add the new headers to the rest of the data, and you are done.

[djr1286]

Apologies.

XX_XXX_YR_MO1_AM ---> RT_RBV_YR_MO1_AM

YY_YYY_YR_MO1_AM ---> OT_RBV_YR_MO1_AM

So only the first character differs. Also, in case it matters, the count section changes in length, from MO9 to MO10. It is a letter 'O' not a zero after the M.

[OTrieger]

@djr1286 
My answer was in relation to your data, so you can figure out how to modify it.

As @ChrisTX  mentioned, share proper data otherwise the answer that you will get will be based on what you provided.

Nevertheless the concept of the solution will not change.