Joining multiple excel worksheets

[gdennis3]

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!

[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.

M.

[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!

[RolandSchubert]

Hi @gdennis3 ,

I think a sequence of joins is needed to attach names and descriptions to the data. 

I've attached a sample workflow. Let me know if it works for you.

Best,

Roland

[atcodedog05]

Thats a lot of joins guess all of them is pretty much needed.

[atcodedog05 · Accepted solution]

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.

[mceleavey · Accepted solution]

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

It's a simple sequence of joins:

M.

[atcodedog05]

Hi @gdennis3 

@mceleavey adds a really good point in his workflow. If you dont want to loose any rows from main (left) data you can use Union on J anchor and L anchor to retains all rows even if it didnt join with IDs