Create a new row by adding rows

[NicoleHou]

I am trying to create a new row by adding/subtracting two rows: I am subtracting row 1520 from row B00030 to get "new row"

1000				75,068,849.29	20.21
1200				17,830.64
1500	9.08	123,139,844.48		647,535,943.85	936,622.96
1520
5020				16,596,474.10
4000
4200
B00030	(534,649,674.31)	121,790,923.38	(270.09)	70,623,266.96	1,817,934.67
6600000	417,366,011.00				495,259.00
6600600	17,872,759.00
6601000	92,483,610.22	12,401.38
6601110	301,225.03	12,886.01
6601120	502.52	145,773.23
B00005	(1,064,689,379.58)	121,631,834.76	(270.09)	70,623,266.96	1,322,675.67
New Row	(534,649,683.39)	(1,348,921.10)	(270.09)	(576,912,676.89)	881,311.71

any help is appreciated!

[DavidSta]

Hi @NicoleHou,

one option would be to calculate across different rows with the Multi-Row Formula tool. But with this you always have to make sure the entries have the same sorting and no new entries are added later.

Attached you can find a sample workflow which can work dynamically by pivoting the table multiple times.

1) As you want to calculate across different rows and you have multiple columns where you want to apply it I recommend to pivot the table. With this you get the items you want to calculate with (plus/minus ...) in columns instead of rows. This happens with the first Cross-Tab and Transpose Tools.

2) Then you can do the calculation very easily with a formula tool.

3) When this is done you pivot this table back to the original format with a Transpose and Cross-Tab. 

4) To get the original sorting order you add the RecordID which was added directly at the beginning to the end and use it for sorting the items.

The general recommendation to create a dynamic workflow for such challenges is:

• when you want to apply formulas: bring the items you want to use for the calculation to columns
• when you want to aggregate something: bring the items you want to aggregate in a single column but multiple rows

Here the Cross Tab and Transpose Tools are your best friend.

Best regards,

David