Trying to clean and join data from excel files with similar data but different formatting

Zikosmith

Hi - I am new to alteryx and am trying to join data sets from multiple excel files.

There are multiple tables to analyse within each excel file. The tables contain broadly similar dimensions and metrics

The first set of excel files included all the tables in one tab.

The tables in the first set of files are not properly formatted and need to be adjusted into a table format and then cleaned. The formatting may include merged cells for example.

The second set of excel files contains one table per tab, all properly formatted. The data needs some light cleaning.

I have created an example excel with the first set of files (all tables in 1 tab) and the second set of files (1 table per tab).

Are there any recommended best approaches to create an overall exile file with one summary table including all the data  for a specific group of data per tab?

Many thanks!

First set of files example.xlsx

Second Set of Files example.xlsx

DavidP

Hi @Zikosmith 

I've take your files and show the 2 different ways of loading the data for each scenario. If I understand correctly, will you have both scenarios and need to combine the data? In that case, you can use the Union Tool once both data sets are in the same format.

If the combined data set needs to be written to an Excel output file with each group in a different tab, that can be done easily from this point.

Let me know if I didn't understand your question correctly or if you have any further questions.

Example workflow attached.

Two ways of loading excel data.yxzp