Hi,
I'm trying to figure out how to create a macro that will give the difference between column 3 and column 4, and then between columnn 5 and column 6, and so on. It's easy enough to use the Multi-Field Formula tool to do this for one column pair, but I have about 180 columns and would rather not have 90 instances of the Multi-Field Formula tool in the workflow. As an example, I'm starting with...
Date | Order ID | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 |
1/1/16 |
156498 | 158 | 785 | 444 | 265 | 164 | 945 |
1/2/16 | 187485 | 145 | 141 | 189 | 779 | 762 | 126 |
1/3/16 | 187698 | 589 | 274 | 189 | 865 | 159 | 753 |
1/4/16 | 485976 | 893 | 568 | 458 | 452 | 168 | 173 |
...and would like to end up with...
Date | Order ID | Column3 | Column4 | Column3_4Diff | Column5 | Column6 | Column5_6Diff | Column7 | Column8 | Column7_8Diff |
1/1/16 |
156498 | 158 | 785 | -627 | 444 | 265 | 179 | 164 | 945 | -781 |
1/2/16 | 187485 | 145 | 141 | 4 | 189 | 779 | -590 | 762 | 126 | 636 |
1/3/16 | 187698 | 589 | 274 |
315 |
189 | 865 | -676 | 159 | 753 | -594 |
1/4/16 | 485976 | 893 | 568 | 325 | 458 | 452 | 6 | 168 | 173 | -5 |
It seems like a Macro would be the best way to do this, but I'm open to other solutions. Thanks for the help.
Solved! Go to Solution.
you can transpose the data with your date and field id. Then add a count to the fields as 0,1 (multi row). Then calc the diff from 1-0 multi rows. You'll have some tricks to crosstab the data back. But this is the way to solve the problem dynamically.
Try with a few fields first. This approach will work for the 180 columns of data.
Sorry, but it is Valentine's Day. Cant work tonight.
Mark pointed right direction .
if you still need some solution then try this one . it should work for Dynamic way :)
Thanks for pointing me in the right direction.
Thanks for filling in that last part. Did exactly what I needed it to.
User | Count |
---|---|
18 | |
14 | |
13 | |
9 | |
8 |