Join Sheets With Same Name Different Files?

[Andre_Marroquin]

Hi Everyone, I have two files, one for 2019 data and one for 2020 data with tabs that are named the same (order of them may be different but majority titled the same). We basically just copy the file over each year and update the numbers. Is there a way to take both files and if their tab names match, then join them into one and do this for each matching tab? I am ultimately trying to create an output that contains all tabs with both year's data for me to do Year Over Year comparisons. 

[ArtApa]

Hi @Andre_Marroquin - This article may help: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Fi... 

[DawnDuong]

Hi @Andre_Marroquin 

you can use a batch macro to do this.

Basically the macro helps you to go down a list of specified files to extract the tab you need. Assuming that the tabs have the same columns etc... the data will stack together nicely.

i recommend you watch the macro interactive lessons to understand how batch macro works to avoid unexpected outputs and/or know what to do to fix an error

dawn 

[mceleavey]

Hi @Andre_Marroquin ,

I've built a couple of tools to help you do this.

The first reads in the sheet names of all files in a given folder location. The second then loads them in and unions them together.

I've attached both macros and an example workflow.

I hope this helps.

M.

[Andre_Marroquin]

bump

[Andre_Marroquin]

@mceleavey ,

Thank you so much for providing this!! Do you know if I am able to select an cell range on the front end and have it apply to all tabs as it joins them together? For example: I'd like it to only select range D16:F78 and then proceed to join everything together. The reason I ask is because there are headers and footers in each tab for notes etc and want to exclude all of these and only keep the data. 

[DawnDuong]

hi @Andre_Marroquin 

You can specify the range in the Input Tool. https://help.alteryx.com/current/designer/input-data-tool

On Select Excel Input, select one of the Excel inputs:

• Select a sheet to choose from the sheets available in the Excel file.
  • Select a range (cells, rows, or columns): Optionally, select a range of cells, rows, or columns within the sheet.
• Select a named range to choose from the named ranges available in the Excel file.
• Import only the list of sheet names to create output with a single column containing sheet names as values. None of the related data is output when this option is selected.

Dawn.

[Andre_Marroquin]

Hi @DawnDuong do you know if this can be executed with the macro that @mceleavey provided?

[mceleavey]

Hi @Andre_Marroquin ,

yes you can, but you would need to amend the existing macros (save new versions of these!).

In the second macro, you will need to select one of the spreadsheets in the Dynamic Input tool to select a range of cells. You would then need to attach an interface tool which would allow the users to select a range, which would then overwrite this value in the Dynamic Input tool.

I would suggest looking into how to build Analytics Apps to help you achieve this.

You can find some resources for this on the community:

https://help.alteryx.com/current/designer/analytic-apps

https://help.alteryx.com/current/designer/apps-and-macros

and @cplewis90 has a good intro in his BLOG.

I hope this helps,

M.