Can Macro be applied to Multi-Row Tool to produce same calculations for different fields?

[jwamendoza]

Dear Alteryx Experts, 

I need your help. I'm using a multi-row formula tool to produce 3 different calculations (based on previous row values) from field name A.

Now, using the same calculation, I need to produce exactly the same results for different fields, i.e. "B" and "C".

Is there a way to apply macro on this matter instead of replicating  the calculation step for "B" and "C" into separate workflows/steps?

Expected Output is like this.

ID	Time	A	B	C	A_Prev1	A_SumPrev2	A_AvePrev2	B_Prev1	B_SumPrev2	B_AvePrev2	C_Prev1	C_SumPrev2	C_AvePrev2
XYZ	M1	12	34	56	0	0	0	0	0	0	0	0	0
XYZ	M2	101	90	78	12	12	6	34	34	17	56	56	28
XYZ	M3	123	145	167	101	113	56.5	90	124	62	78	134	67

I'm attaching a dummy file for your reference.

I hope someone has a solution to this.

Thank you.

[kathleenmonks]

Hi @jwamendoza 

Instead of using a macro, you can transpose your data, run the calculations and then crosstab your data back. Attached is your workflow showing how this would be done. Some sorting is needed to ensure the calculations happen as you would expect and I added a formula tool to create the column headers before the crosstab. If there are only 3 columns (A,B,C) this might not be the most elegant solution, but if you have more and more columns, this scales more effectively than copy/pasting three multi row formulas per column. 

Let me know if this helps!

[flying008 · Accepted solution]

Hi, @jwamendoza 

First, there is a resolution with Multi-Field & Multi-Rows macro in gallery. (All field names and order can be output automation as you want.)

****************************

The second, you can use the way like @kathleenmonks  to get the result, all are auto like above macro output.  Don't worry even if you have more columns to be process.

[jwamendoza]

Thank you @kathleenmonks and @flying008 for sharing your solutions. :)

[flying008]

Hi, @jwamendoza 

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.