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

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?

Dynamic Processing

Accepted answers

Adrian_T

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.

Insert in defined range.yxmd

Adrian_T

Hi @db89, as mentioned previously, the workflow inserts the copied rows (dynamic number of rows) into your Template file. However, given the need to overwrite/drop your existing Main Template file in order to do this, it is not possible to retain your existing formulas in the file. The best workaround will be to include your formula logic as part of your Alteryx workflow, and output the results (not the formula) in the output file.

I have attached an example workflow which does not produce your mentioned error, do give it a try. Do note that formulas will not be retained.

Insert in defined range.yxzp

All comments

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.

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.

Main Template.xlsx

Source.xlsx

Adrian_T

Hi @db89,

Hope this helps.

Insert in defined range.yxmd

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.

db89

Hi @Adrian_T,

Exactly! That is what I was trying but unfortunately, I am getting the attached error when I am trying to get the output data in the Main Template file.
The basic logic behind it is, it has to go in to the Main Template file, in just the exact place where the data needs to be pasted (that is between the "Insert time below this line" and "Insert time above this line" area).

And of course, when that gets done the formulated parts should stay intact. Will you be able to share the same workflow please, so that I can understand how you are configuring the output?

ss1.png

ss2.png

db89

Hi @Adrian_T,

ss1.png

ss2.png

Adrian_T

I have attached an example workflow which does not produce your mentioned error, do give it a try. Do note that formulas will not be retained.

Insert in defined range.yxzp

db89

Hi @Adrian_T,

Thank you so much for your time and guidance! I can't thank you enough for getting back on this. I have read your note that formula will not get retained, so have managed an alternative way for that. None the less, thank you again! Will keep in touch if I have more issues.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703