Macro to compare consecutive pairs of columns

[jjc42]

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.

[MarqueeCrew]

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. 

[s_pichaipillai · Accepted solution]

@jjc42

Mark pointed right direction .

if you still need some solution then try this one . it should work for Dynamic way :)

[jjc42]

@marqueecrew

Thanks for pointing me in the right direction.

@s_pichaipillai

Thanks for filling in that last part.  Did exactly what I needed it to.