Join data horizontally from multiple sheets within the same excel file

Question

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.

vikaschandra1993 · Answer

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

CharlieS · Answer

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.

20211109-DynamicPositionJoinUse.yxzp

CharlieS · Answer

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.