Joining multiple excel worksheets

I'm trying to combine data from 5 different excel worksheets, all with different columns. For instance, one worksheet contains columns labeled "Business Unit, Business Unit ID, and Business Unit Description" while another worksheet contains columns labeled "Preparer Name, Preparer ID, and Business Unit ID"

Any suggestions for how to combine all these worksheets into one output that features all of the columns and combines the data within them?

Thanks!

Join

Accepted answers

atcodedog05

Hi @gdennis3 ,

Pretty sure most straight solution for this would be to join one by one.

Here is a workflow for the task.

Rename or reorder your columns in the last Join tool Select Pane.

Hope this helps : )

If this helps please mark the post as solution.

Multi-joins.yxmd

mceleavey

Apologies for the delay @gdennis3 but I think other have nailed it.

It's a simple sequence of joins:

Solution.yxmd

All comments

mceleavey

Hi @gdennis3 , there are multiple ways of doing this. First, if the sheets are different but are all in absolutely consistent formats, you can load them in then use the union tool. In the union tool, select the dropdown which is currently showing "Auto config by name" and select "Manual configuration". Then select which columns you wish to highlight as the same. This will effectively achieve the same as copying and pasting the records below each other in the correct columns (if you're thinking like Excel).

If you wish to join them more like a vlookup, then use the "Join" tool and select the key field in each input, int his instance it looks like Business Unit ID. This will then join the inputs together and match on all matching records (not just the first match, as in a vlookup)

I hope this helps. If you want me to build this for you just post your data, or mock data, and myself or someone else will do it for you.

gdennis3

Thanks for the input! That has definitely helped and I've been able to make some progress.

I am attaching the excel workbook with the 5 worksheets and also a screenshot of the first three rows that I am supposed to end up with. If you or someone else has time to at least point me in the right direction so that I know what I've been doing is on the right track that would be great.

Thank you!

Analytics_mindset_case_studies_ETL_Case4_Alteryx.xlsx

Screen Shot 2020-09-30 at 10.35.39 AM.png

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

hhaigh1 280