Leaving Exact Excel formulas in Alteryx

[Luqman]

Hi

I have a few hundred columns of Excel formulas that I need to cover to alteryx

Doing so will take weeks/Months. So my current solution is just to leave it as an excel formula for users to open and refresh

Sample formula

=IF(ISERROR(CJ7),"",IF(OR(LEFT(CJ7,3)="not", LEFT(CJ7,3) = "dec", LEFT(CJ7,3) = "cea"),"", IF(LEFT(B7,4)="CGIS",CJ7/30000,IF(CJ7<>0,1,0)*IE7)))

How do i "drag" this formula , like in excel, so that each new row will be CJ8, CJ9.. and so on


Data rows will reach millions*

[CatheyH]

It isn't clear how you are planning to handle the excel file & data -

Do you want to leave the data in the excel file, paste in data from Alteryx, forcing the formula to copy down a variable number of rows in Excel? If so, consider using an Excel Table format. Make sure there is one row containing dummy data and your formula. Alteryx should be able to paste into the table and the table will do the calculations for you.

Or are you somehow wanting to bring that data into Alteryx? If so, Alteryx won't be able to understand what you are asking it to do. You could build something based on a complex multi-row formula, but it would only update the text in the formula and wouldn't actually do anything. It would be easier to build the formula - IF/OR/left/right functions are very similar. There's also the advantage of being able to "nest" your formula in different variables in one formula tool so that you don't have to do it all in one go.

[Luqman]

Hi @CatheyH 

> Do you want to leave the data in the excel file, paste in data from Alteryx, forcing the formula to copy down a variable number of rows in Excel? If so, consider using an Excel Table format. Make sure there is one row containing dummy data and your formula. Alteryx should be able to paste into the table and the table will do the calculations for you.

Like this.

do you mind giving me a sample for this? I don't know how to do that. 

[Qiu]

@CatheyH 
Is this article good for you?
https://dev.classmethod.jp/articles/extract-excel-forumla-en/

[CatheyH]

@Luqman Well this turned out to be a rabbit-hole! Apparently Alteryx will not automatically extend a table like it would if you just paste in. I learned something new today!

The way to get around this is to populate the formulas in Excel to the extent of the largest possible data extract. You can then drop in the data from Alteryx and it will calculate what you need, leaving the unused formulas below.

Screenshot example below.

Write to File: the range here should be the max size that the Alteryx output could be. In my example there are 4 columns and a lot of rows. The range should paste on the row below your column headings, so be sure to select "Skip Field Names"

The Alteryx output range and the number of fields with formulas in Excel don't need to sync for the paste to work, but you might want to keep them aligned to avoid missing any lookups.