Join many tables in the same sheet in Excel file

[Gaby]

Greetings,

I am completely new in Alteryx and would like to join many tables present in the same Excel Sheet.

Basically, I have the data like this:

Product    Price  Country1               Product    Price    Country2     Country3

P1             55          1                         P98           58          2                  3

P5             30           2                        P3             20          0                  1             

Each table has more than 500 records and approximately 10 columns. Under the country column, we have the units for that country. 

The aim if to transform all this in one table and one file with a column country having the country and a column units having the units.

I join a copy of the type of file.

Any idea of how this can be done?

Thank you very much in advance.

[TylerNa · Accepted solution]

The attached workflow should get you on your way!

[Gaby]

Thank you very much for your help, but I need something that can be automated. I mean I wont have to manually select all the columns. I have more than 10 tables per sheet with 10 columns each. I am trying not to go to 100 table to tick the right one.

[alexey_nikityuk]

Gaby, i think prior solution makes sense as 1) there is one button how you can select all columns to be included and 2) yes, you would need to manually exclude those empty columns but from your description it seems like there are no more than 10-15 of those columns, so i think you may try to use what Tyler proposed

[Gaby]

Thank you! You were right! I tried and it does not take time. pretty straight forward!

[Gaby]

Thank you very much! I t works perfectly!

[TylerNa]

@Gaby if you do end up needing a dynamic way of removing null columns I found this really helpful macro that @Joe_Mako developed that does just that!

Delete Null Rows or Columns Macro

[Gaby]

Thank you! @TylerNa