Join data horizontally from multiple sheets within the same excel file

[matthias1808]

Hi,

I am trying to join my data horizontally from multiple tabs coming from the same excel file. I have seen answers for macros to combine data from multiple sheets and/or files vertically as the column headers are consistent, like a union. Is there a way to do the same but combine data horizontally like a join tool would? Or is it possible to transpose data from multiple excel sheets in one go and then use a macro to union the data?

Thanks in advance.

[ChrisTX]

See if the Join Multiple tool works for you.  It can perform a full outer join, or an inner join.

Here's the tool mastery article:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Join-Multiple/ta-p/124...

[CharlieS]

Hi @matthias1808 

Typically in cases like this there's some sort of key/ID field that can be used to Join records from multiple sources. If you want a position-based Join, both the Join and Join Multiple tools can be configured to perform this on the inputs. Here's what the option looks like at the top of the tool cofiguration:

Now that being said, this will work if you want to manually configure the inputs to these tools. If you want this to be a dynamic process, that should be possible with some transformations to the data like you alluded to. 

[matthias1808]

Thanks for the suggestion. Forgot to mention there is a key ID field in the first column which makes the join easy. My issue is having to manually set up input tools to link to the join multiple tool for each excel sheet I want joined together, which is not  feasible for dozens of sheets. Would you have other suggestions of making joining dozens of sheets with the same key id column more elegant? Any help is appreciated, thanks.

[CharlieS]

Sure thing. 

In that case, I'd recommend to Transpose each sheet into Name:Value pairs, then this can be unioned together from all the sheets (via batch macro). Once all the data is combined, you can Cross Tab back to the original format, but you'll need to think about what to do with duplicate field names (if there are any).

Do you have an example Excel file? If not I'm sure an example can be created to show how this can be achieved. 

[CharlieS]

Here's a quick example of how we can use a batch macro to read and transpose a dynamic number of sheets before using a CrossTab to rearrange the data at the end. Check out the attached Alteryx package (include a workflow, batch macro, and Excel file input) and let me know if this all makes sense.

[vikaschandra1993]

Hi Charlie

Is there a way to do same if the sheets are in multiple files within directory? I have a directory with multiple csv files, each has a table with common id but different attributes. I want to combine all data with common id and attributes in different columns. 

File 1: ID, Attribute 1

File 2: ID, Attribute 2

File 3: ID, Attribute 3

The output should should have ID, Attribute 1,Attribute 2, Attribute 3 as columns