Retain excel formula in Alteryx input and output

Hi All,

I do not know whether it is possible in Alteryx or not. If yes, it is amazing.

I have a sample data as attached where in first sheet (Sample) contains some excel formula in columns C, D, E, F, G which is linked with 2nd sheet (Port List).

I just want to do find and replace in column A in Alteryx, and get output in the same column. When I output in the same column in the same file then Columns C, D, E, F, G would automatically update values as there is formula. If the question is not clear (please download the Sample data, and do find and replace (replace Mukalla with Mukalla Inland) in Column A in the first sheet and see how it works.

So, how can I get output where excel formula remains and I get the work done in Columns C, D, E, F, G by just doing find and replace in Col A with the help of Alteryx.

Sample Data (1).xlsx

Output

Accepted answers

allwynthomas24

Hey @Rahul_Analyst,

Please check the attached solution and confirm whether it matches your specified requirements.

Spoiler

If the workflow solved your problem, please do consider to accept it as the solution.

Regards,

Allwyn Thomas

Single Column Update - Rahul.yxzp

All comments

jordanmount6

You could try this solution:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Is-it-possible-to-retain-a-formulas-in-a-cell-and-displaying-it/td-p/758890

Rahul_Analyst

Hi, @jordanmount6 thanks for your response.

I went through all the posts, I think I need to do some more research on it. But, you gave a hope that it is possible.

One challenge, I am also facing that, In my sample I want two sheets.

Is there a way where we can keep this excel file in a folder then define the same path in output tool or define range in output tool where we want to do find and replace and change configuration to overwrite, then find and replace will be done only in col A and other columns and sheets remain same as it is ?

I would appreciate, if anyone can try to make a workflow, it would be very helpful in understanding.

allwynthomas24

Hey @Rahul_Analyst,

With the help of the posts from
@danilang (https://community.alteryx.com/t5/Alteryx-Designer-Discussions/comparing-column-headers-and-notes-in-header-cells-to-see-if-two/m-p/758040/highlight/true#M182762)

& from @MattD (https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Writing-Formulas-to-Excel/ta-p/12337) ,
I have somehow tried to retain formula for adding 2 numbers from Column A & Column B. I know this is not the exact solution you are looking for, but check whether it can add any further enhancement to your idea. You will have to research and play alot with the XML part to get your formula.

Thanks to @danilang & @MattD for sharing their ideas and resources which was very helpful in understanding the XML part & how to retain the formula in the output.

@Rahul_Analyst, Do check the attached workflow. It may give some more hopes and help you to move forward with your problem.

Regards.

Excel Retain Formula - AllwynThomas.yxzp

allwynthomas24

Hey @Rahul_Analyst,

Please check the attached solution and confirm whether it matches your specified requirements.

Spoiler

If the workflow solved your problem, please do consider to accept it as the solution.

Regards,

Allwyn Thomas

Single Column Update - Rahul.yxzp

Rahul_Analyst

@allwynthomas24

This is the best solution for my problem without using XML. Thanks for your time.

allwynthomas24

Hey @Rahul_Analyst,

Thank you very much for accepting the solution.

Glad that you had posted the query as it also helped me to explore the XML part and quite challenging to come up with the requirement solution.

Happy to help.

Keep Solving.

Thanks & Regards.

Allwyn Thomas

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703