Hi All,
Do you have a working solution for the below problem?
My data looks like this now:
Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 |
Value 1 | Name 1 | 5 | 9 | 8 | 0 |
Value 2 | Name 1 | 3 | 9 | 3 | 2 |
Value 3 | Name 1 | 12 | 13 | 10 | 0 |
Value 4 | Name 1 | 18 | 3 | 2 | 0 |
Value 1 | Name 2 | 20 | 13 | 11 | 10 |
Value 2 | Name 2 | 16 | 16 | 11 | 2 |
Value 3 | Name 2 | 11 | 5 | 5 | 10 |
Value 4 | Name 2 | 15 | 6 | 5 | 17 |
The layout I would like to get:
Name 1 | Name 1 | Name 1 | Name 1 | Delta | Delta | Delta | Delta | Name 2 | Name 2 | Name 2 | Name 2 | |
Column 1 | Column 3 | Column 4 | Column 5 | Column 6 | Delta Column 3 | Delta Column 4 | Delta Column 5 | Delta Column 6 | Column 3 | Column 4 | Column 5 | Column 6 |
Value 1 | 5 | 9 | 8 | 0 | -15 | -4 | -3 | -10 | 20 | 13 | 11 | 10 |
Value 2 | 3 | 9 | 3 | 2 | -13 | -7 | -8 | 0 | 16 | 16 | 11 | 2 |
Value 3 | 12 | 13 | 10 | 0 | 1 | 8 | 5 | -10 | 11 | 5 | 5 | 10 |
Value 4 | 18 | 3 | 2 | 0 | 3 | -3 | -3 | -17 | 15 | 6 | 5 | 17 |
Thanks for the help.
Solved! Go to Solution.
Hi @Pisti93,
Your problem is about transposing and making cross tab and transposing to get the data formated how you want. The trick in this case, is mostly to focus to have a dataset formated in columns, then create the calculations needed and finally transpose and cross tab again to have it presented as you want.
Here are detailled steps :
If explanations are unclear, tell me
This solution worked as charm. Thanks for the quick response. Do you have a solution in case the number of "Name" is more than 2? I mean now only "Name 1" and "Name 2" exist but what if also "Name 3" is there? (Also we can't be sure about the exact number)
Delta1 should be the same as now (like in your solution):
Delta2 should be [3] - [2] in case [3] exists, Delta3 should be [4] - [3] in case also [4] exists and so on...
In this example the number of delta columns is dynamic depending on the number of other columns. Is it possible to solve it without writing a macro in python?
Thanks!