(Efficiently outputting) data from multiple tabs in one file into tabs in another file

Question

Say I've got two excel files : file1.xlsx (three sheets: SheetA, SheetB, SheetC) and out.xlsx (two sheets: Sheet1, Sheet2)

I want to copy the data from SheetA and SheetB into Sheet1 and the data in SheetC into sheet2.

This is fairly simple, this is how I do it :

(the text tool contains the path+range of the three sheets)

This works fine, but doing this at a larger scale makes the workflow fairly congested and ugly. There's gotta be a more efficient way to do this right?

I suspect reporting tools might be of help here but I don't know how exactly. Anyone care to help out?

abacon · Answer

@vj28 For the output piece - output tools have the option to send data to different sheets based on a field or criteria.

check out this post on how to do it. If you use both of these processes in unison, it will be super efficient and clean looking on the workflow.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Group-output-into-seperate-sheets-in-one-Excel/m-p/1386559#M343850

if these work for you, please mark them as the solution so others may find it easier.

Bacon

abacon · Answer

@vj28 Yes there is a much more efficient way to do this. When you are trying to get data from multiple sheets or tabs, dynamic input is not as good due to needing the schemas to be identical.

Look at this thread and see the attached files and links, I provide a macro that gets the sheet names from all files in a directory then gets all the data from those sheets.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Loading-Multiple-Workbooks-with-multiple-Worksheets-Filtering/td-p/1392167

This is a handy macro to have saved as it can be configured to work with CSVs and SharePoint as well.

Bacon