Adding Multiple Fields based on name similarities

Question

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.

NameA1A2A3A4B1B2B3B4John

11111111Jane11111111Jack1

1111111Jill11111111

OTrieger · Answer

@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.

djr1286 · Answer

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 · Answer

@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.