Leaving Exact Excel formulas in Alteryx

Question

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*

Sample.xlsx

CatheyH · Answer

@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.

Qiu · Answer

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

Luqman · Answer

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.