Very tricky task at hand: Multiple tables into one master table (different headers in each column!)

[Baileyy]

Alright so as the title says, my task is to combine around 100 tables into one giant table. The first 3 headers will be the same for them all and then the next 2-10+ headers depending on the data in that table will all be in different orders. I would like them all to match up in one layout which will display every table in one giant table neatly. My tables look like something listed below and I want the master table to contain every header found in each table (only the first 3 headers will be the same each table) and lay it out as the 3rd example table provided. the headers will have to expand up to around 50-60 columns and I have 1500 rows of values and sites to line up with these headers.

Table1:

IDENT <tbody> </tbody>	Job <tbody> </tbody>	Project code <tbody> </tbody>	pH <tbody> </tbody>	EC <tbody> </tbody>	Alkalinity <tbody> </tbody>	TSS	NO2	HARDNESS
UNITS
SCHEME
Site1			1	2	3	4	5	6
Site2			8	9	10	11	12	13
Site3			15	16	17	18	19	20
Site4			22	23	24	25	26	27
Site5			29	30	1	2	3	4
Site6			6	7	8	9	10	11

<tbody>
</tbody>

Table2:

IDENT <tbody> </tbody>	Job <tbody> </tbody>	Project code <tbody> </tbody>	Mg	OH	Cu	Cd	Fe	I	Mn
UNITS
SCHEME
Site7			44	35	63	74	74	28	91
Site8			53	45	65	131	12	13	14
Site9			15	16	157	138	49	20	21
Site10			23	23	24	25	26	27	23
Site11			93	30	12	23	32	43	54
Site12			63	47	48	93	10	11	14

<tbody>
</tbody>

Result I want the mastertable to look like:

IDENT <tbody> </tbody>	Job <tbody> </tbody>	Project code <tbody> </tbody>	Mg	OH	Cu	Cd	Fe	I	Mn	pH	EC	Alkalinity	TSS	NO2	HARDNESS
UNITS
SCHEME
Site1			44	35	63	74	74	28	91
Site2			53	45	65	131	12	13	14
Site3			15	16	157	138	49	20	21
Site4			23	23	24	25	26	27	23
Site5			93	30	12	23	32	43	54
Site6			63	47	48	93	10	11	14
Site7										1	2	3	4	5	6
Site8										8	9	10	11	12	13
Site9										15	16	17	18	19	20
Site10										22	23	24	25	26	27
Site11										29	30	1	2	3	4
Site12										6	7	8	9	10	11

<tbody>
</tbody>

The sites will go down to about Site1500 and the columns will stretch about 50 to the right.
Sites 1-6 do not contain any values to do with the other headers so I would like a gap between the 'Mn' column and the column that will come after hardness (if there was more values of Site1-6 to display)
Basically wanting a way of it detecting the header for each table and making a master table without duplicating each header then laying out the sites in the left columns going down and aligning the values with its top line headers while remaining on its Site# line.

I hope I explained it clear enough, pretty much 15 tables of different data and headers just combined into one massive table to be able to look at 100+ tables in one master overview.

 

[Baileyy]

pretty much 15 tables of different data and headers just combined into one massive table to be able to look at 100+ tables in one master overview.

100 tables into one to view all of them in one big overview. sorry for wording it complexer than it needed.

 

[Baileyy]

Also I listed the values wrong (site 1 on the result table should be swapped with site 7 etc) but the principal of the task is still the same. as long as the tables output it into a shape like that.

 

[DHayes]

Have you tried using the consolidate function in the data tab. I found this worked for me when there are different headings.