How to do this?

[HA]

Hi ,

I have a dataset with mutiple columns and I need to add a calculated fieild and change the format as follow.

Original dataset

ID	Date	Value
AAA	11/1/2016	5
BBB	11/1/2016	3
AAA	11/2/2016	7
BBB	11/2/2016	2
AAA	11/3/2016	4
BBB	11/3/2016	3

I need to change it to

ID	ID2	Date	ValueAAA -Value BBB
AAA	BBB	11/1/2016	2
AAA	BBB	11/2/2016	5
AAA	BBB	11/3/2016	1

[Federica_FF · Accepted solution]

HI!

What you need is basically to summarize your data grouped by date.

In the attached workbook you can find 2 different approach. The first one treats the values numbers as strings, concatenates them and then split them again in order ro use a formula to perform the formula Value A - Value B.

The second one, consists in an if statemement that converts into negative numbers all the BBB values, so that you can easily summarize the rows with the sum method.

Take a look at the workflow!

[HA]

Thank you I will use the seocnd approach.