Calculating the difference across columns for a fixed row length.

Hi,

I have an excel sheet that has columns as Jan, Feb, March up to December as shown in the image below.

I would like to calculate the difference between A & B (A-B) for January, February, up to December on row 4, then the difference between C&D (C-D) for January, February up to December on row 7. I would like to follow the same pattern up to row 730.

Can somebody please suggest a macro-free way of performing the calculations?

Thank you in advance!

Preparation

Accepted answers

Maskell_Rascal

Hi @Jack6627

I would highly recommend checking out this macro that @patrick_digan created: https://gallery.alteryx.com/#!app/Multi-Row-Multi-Field/59639862effc2a0c04c7e653

It combines the functionality of both the Multi-Row and Multi-Field formula tools.

If you don't feel like downloading this macro, here is how you can do it with the standard tools.

Let me know if this works for you.

Cheers!

Phil

Calculate Across Columns for Fixed Row Length.yxmd

All comments

Tyro_abc

I think the Multi-Row Formula tool is useful for this. It takes time to solve because I don't have the data.

Regards

Arundhuti

JoaoLeiteV

Hey there,

I made up an example where I'm using a multi-row formula to do the calculation if the word "Difference" is found. This means that for every line with Difference, it''ll take up the two numbers above and subtract them.

In this method, you'll need 1 multi-row formula tool for each month, but you won't be needing a macro.

Let me know if this can help you out or if you have any questions!

Example.yxmd

Maskell_Rascal

Hi @Jack6627

I would highly recommend checking out this macro that @patrick_digan created: https://gallery.alteryx.com/#!app/Multi-Row-Multi-Field/59639862effc2a0c04c7e653

It combines the functionality of both the Multi-Row and Multi-Field formula tools.

If you don't feel like downloading this macro, here is how you can do it with the standard tools.

Let me know if this works for you.

Cheers!

Phil

Calculate Across Columns for Fixed Row Length.yxmd

Jack6627

This worked! Thank you @Maskell_Rascal. I am a beginner to Alteryx and hence wanted a macro free solution. I will take a look at the macro as I get a deeper understanding of Alteryx. 🙂

Tyro_abc

Try this...

Regards

Tyro

Calculating the difference across columns for a fixed row length.yxzp

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335