Combine columns

Question

Hi, I'm trying to complete an exercise but I couldn't seem to make it work. What I wanted to output is two columns for Exp and Dis with the corresponding row values in order from 1-9.

Exp                     Dis

1513                  28,959

403                   1315

..                       ..

I'm attaching an input sample for reference, appreciate the help.

SampleAO.xlsx

TheMattLeonard · Accepted Answer

Start by using a Transpose Tool and leaving all of the Dis and Exp fields as your data fields.

From here, you can use a formula tool to label each item as Dis or Exp and also label the row number. Make a new field, name your new field [Dis/Exp], and make your expression "IF Contains([Name],"Dis") THEN "Dis" ELSE "Exp" ENDIF". And another expression onto the formula, name it [Row Number] and make the expression "Right([Name],1)" (if you go beyond 1-9 and have two digit numbers you'll need to update this).

Finally, use a crosstab tool. Group on [Row Number], change column headers to [Dis/Exp], and select [Value] as the values option. Select "Sum" as your aggregation.

I believe this should do the trick. Hope this helps!