Macro to compare consecutive pairs of columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Macros
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Mark pointed right direction .
if you still need some solution then try this one . it should work for Dynamic way :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for pointing me in the right direction.
Thanks for filling in that last part. Did exactly what I needed it to.