Data import from multiple excel file with specific format

I've creating a work flow to import data from all the different spreadsheets onto one spreadsheet with specific fields. The files that I'm looking at inputting is as follow: -

Excel 1: the headers of the file that the data from subsequent file should be input to.

Excel 2: Data under first line of the Consolidated column of each individual to be mapped to header (A) in Excel 1, second line of the Consolidated column of each individual to be mapped to FIN no field in Excel 1

Excel 3: Data set with information with the difference between columns, A and B, and, C and D to be mapped to column G of Excel 1, and column J of Excel 1respectively.

Excel 4: Data set with information with the difference between columns A and B to be mapped to E of Excel 1.

I've tried to union the data in Excel 2 with the respective headers in Excel 1 but is unsuccessful. To illustrate the above, I've attached my workflow and the screenshot of Excel 1 and 3 for reference.

Appreciate if anyone can provide a fix to the workflow?

Sample data - .png

Cleaned data test.xlsx

Output Matrix (1) test.xlsx

Data import.yxmd

Datasets

Input

Output

Dynamic Processing

Join

Accepted answers

TonyA

I think I finally sorted this out. I've attached a workflow that gets you most of the way there. There are fields in the output matrix that weren't defined, so a lot of the calculations couldn't be done, but I set this up so you have all the available data in one place and once you get the remaining data, the calculations should be easy from there. This turned out to be a much larger project that I expected. Normally for a full project like this you should be working with a local partner or your local Alteryx team. On Community, we generally help with more specific questions like why a tool isn't working as expected or how to do a specific function or task.

Look through the attached workflow. I made a number of assumptions that might not be correct, so I expect there are things you will need to tweak. If you have specific questions about certain techniques I used or how a tool works and you can't find help in the product documentation or in the existing community content, then please post your question. But I think you have a good start with this workflow. There are several columns in the output matrix, specifically J, K, M, and O, that have no instructions or the instructions are either unclear or incomplete. I think that if you sort through those, you can finish this pretty quickly.

Data Import 2.yxzp

All comments

TonyA

I'm assuming from your example that you only want to address steps 1 and 2 and populate Name, FIN, and (A).

There are a couple of interesting aspects to the data that I note in the attached workflow. The trickiest part is that you have two rows with header data and the first row has merged cells that have to be applied to multiple headers. I highlighted the section where I handled that. I think you should also review how the Crosstab tool works and investigate the Dynamic Rename tool for changing field names. Take a look at the workflow and I would also suggest you check out the following links:

Good overview of Alteryx tools for Excel users that discusses crosstabs: https://community.alteryx.com/t5/Videos/Intro-to-Alteryx-for-Excel-Users/m-p/45398

Crosstab Tool Mastery: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Cross-Tab/ta-p/4368

Dynamic Rename Tool Mastery: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Dynamic-Rename/ta-p/32920

Data import pt1.yxzp

LFLee

Hi @TonyA, thanks for sharing. I need to address all points including the import of excel 3 and 4 into the respective columns in Excel 1 apart from steps 1 and 2 and populate the name and FIN.

How do I go about doing that? I was trying to do it with the join multiple function but I can’t get the results I want.

Any suggestions on how to do it?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703