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 |
Solved! Go to 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!
Thank you I will use the seocnd approach.