Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

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

Zikosmith
5 - Atom

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!

7 REPLIES 7
DavidP
17 - Castor
17 - Castor

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.

 

DavidP_0-1624382170119.png

 

Zikosmith
5 - Atom

Hi David,

 

Thanks for this - this is what I was looking for.

 

My challenge was the steps to get the data in the same format using alteryx, especially because one is not really formatted as a table.

 

Thanks again!

 

Ziko

DawnDuong
13 - Pulsar
13 - Pulsar

Hi @Zikosmith 

By “get the data in the same format”, can you elaborate on what kind of differences and light formatting do you expect to have to fix?

For example, are the columns not having the same headers? Or the positions of the columns are mixed up?

depending on the kind of issues, the fixes would be different. Best if you can give examples of the formatting issues so that the community members can better help you 

Dawn 

Zikosmith
5 - Atom

Hi Dawn,

 

Thanks for the response!

 

The main difference between the two excels in my mind are that the second one has a single topic per tab and is already in a table form.

 

The first excel contains multiple topics in one tab. So the topic one table will be in the top 10 cells for example, then topic 2 will be in the following 10 cells.

 

The first excel document is thus not formatted like a table similar to the second excel document.

 

So yes the columns are mixed up. Some of the headings are in different places.

 

I would want to transform the second excel so it looks like the first one, and then combine the documents together.

 

Does that make sense?

 

Thanks again for the response!

 

Ziko

DawnDuong
13 - Pulsar
13 - Pulsar

Hi @Zikosmith 

i have fixed something similar to what you described. Sounds like you have something like a segmental KPi reports (ie output) that you want ro reverse-engineer to the underlying source data (just my guess) that is properly formatted for your sumif or pivot to work.

depending on how the “breaks” are signposted to visually identify when one table ends and another table starts, the steps can be different 

if possible, please attach a sanitised sample data of the 1st and 2nd excel files so that the community can offer more exact (and hence more helpful) advice. Otherwise there are just infinite number of ways of how these “breaks” are identified which makes it next to impossible to narrow down which formats are uses in your case.

dawn 

DawnDuong
13 - Pulsar
13 - Pulsar

Just to add, the reason why i ask for a sanitised data js because the sample excel you attached do not contain the issues you mentioned about mixed up columns etc.. @Zikosmith 

Zikosmith
5 - Atom

Hi Dawn,

 

Thank you so much for the response! And it has been really helpful - I am still learning the ins and outs of this so hopefully this will help with my learning and I will be contributing in the future too!

 

I will share a sanitised file shortly.

 

Many thanks,

 

Ziko

Labels