Join Sheets With Same Name Different Files?

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.

Developer

Accepted answers

All comments

ArtApa

Hi @Andre_Marroquin - This article may help: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Files-with-Different-Field/ta-p/13370

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.

Multi Sheet Import Example.yxzp

Dynamic Multi File Sheet Names.yxmc

Import Multiple Schema Flat Files.yxmc

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703