Copy paste data in existing tab based on the data volume of the source?

[db89]

Hi guys, is there a way I can copy paste a range of data in an existing tab based on the data volume of the source?
For Ex. Excel  sheet A has tab X, where I need to paste the data in range starting from column A13 from Excel sheet B which has the desired set of data and needs to be pasted in Excel  sheet A has tab X? The range might vary, hence can a workflow do the job? 

[Adrian_T]

Hey @db89, this sounds very doable on Alteryx, I was expect your workflow build to look something like connecting to both excel sheets A and B through 2 Input Data Tools, before blending them together using a Join Tool and writing it back to excel sheet A using an Output Data Tool.

2 Input Data Tools connecting to Excel A and Excel B -> Join -> Output to Sheet A

Of course, depending on your business logic you could add more data preparation steps in between. Hope this helps!

[db89]

Hi  @Adrian_T,
Thanks for the quick response! I have attached the two sheets, would you be able to share a workflow sample? Because 'Main template' has a specific range where additional blank rows has to be inserted to paste data from the 'Source' file. Also, at the bottom level of the 'Main template' has formula which should not be disturbed, hence all data from 'Source' file needs to be added in the the 'Main template' in between a range.

[db89]

Hi  @Adrian_T,
Thanks for the quick response! I have attached the two sheets, would you be able to share a workflow sample? Because 'Main template' has a specific range where additional blank rows has to be inserted to paste data from the 'Source' file. Also, at the bottom level of the 'Main template' has formula which should not be disturbed, hence all data from 'Source' file needs to be added in the the 'Main template' in between a range.

[db89]

Hi @Adrian_T
Thanks for the quick response! I have attached the two sheets, would you be able to share a workflow sample? Because 'Main template' has a specific range where additional blank rows has to be inserted to paste data from the 'Source' file. Also, at the bottom level of the 'Main template' has formula which should not be disturbed, hence all data from 'Source' file needs to be added in the the 'Main template' in between a range.

[Adrian_T · Accepted solution]

Hi @db89,

The complexity introduced here is your requirement to keep existing formulas in the excel. This would mean Alteryx has to write into a defined range instead of overwriting the entire sheet, which is possible if your existing template has the required number of rows for you to copy the Source rows into. It currently only has 1 placeholder row.

Since Alteryx cannot exactly "Insert Rows" in excel without overwriting the entire sheet, I would recommend that you consider if the "formulas" at the bottom of the sheet can be calculated in Alteryx.

I have attached an example demonstrating how you can dynamically "insert" the data rows from Source file into the required range in your Template file, but this would mean formulas at the bottom will be overwritten. Should you determine that the formula can be calculated in Alteryx, you can simply add on your required logic to the back of the attached workflow.

Hope this helps.

[db89]

Hi @Adrian_T,
This works like a charm! Thank you so much for your help. Would you be able to guide if the output can be overwritten in the same tab of the "Main Template" file (that is without creating a separate sheet as an Output). 

Thanks, 
db89

[Adrian_T]

Sure, so in the configuration pane of the Output Data Tool, go ahead to point the file path to your Main Template file, and change the Output Option to “Overwrite Sheet or Range”. That should do the trick!

[db89]

I did that same thing, but I thought I was going wrong. Thanks for confirming! But unfortunately, since there are formula at the bottom section, it is not replacing them and throwing an error.

Another question is even if it is successful overwriting the data, will it create/insert rows as per the volume of the data range from source file?

[Adrian_T]

Hey @db89, yes since the workflow blends both data sources by Union, the data “copied” over is dynamic and follows the source file.