Combine multiple tables of varying lengths on a single sheet to two outputs for lookups

[JLMig]

Hello,

I have a few excel files with multiple tables on a single sheet. The tables can be varying lengths (widths will always be the same) and positioned at varying locations from one file to the next, but they will always be in the same order. I would like to combine them into 2 tables to prepare them for lookups. 

Is something like the outputs possible?

[caltang]

Hard, but possible.

[flying008]

Hi, @JLMig 

Yes, it's possible, someone already build the batch-macro for get your want before 2 years, but so complex.

[caltang · Accepted solution]

I went down a rabbit hole and did something complex for you. Hope this gives you some ideas.

[Pang_Hee_Choy · Accepted solution]

my solution will be to manipulate the headers.

identify tables name, keep 3 columns each, assign columnID and table type, then join with the transposed data. 

and finally filter for each table and crosstab back to align the data. 

it may complex, but it less tools. 😁

[JLMig]

Thank you! I'm just getting started with Alteryx, so I appreciate all the help I can get.

[JLMig]

Thank you!

[caltang]

I'd appreciate it if you can mark my solution as an accepted solution as well since it splits the tables nicely for you into separate chunks in proper order.

Thanks @JLMig !