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

B

Baileyy

New Member
Joined
May 20, 2016
Messages
3
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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl98, width: 126"]IDENT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl98, width: 74"]Job[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl98, width: 113"]Project code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl98, width: 64"]pH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl98, width: 64"]EC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 72"]
<tbody>[TR]
[TD="class: xl98, width: 72"]Alkalinity[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TSS[/TD]
[TD]NO2 [/TD]
[TD]HARDNESS[/TD]
[/TR]
[TR]
[TD]UNITS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SCHEME[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Site2[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Site3[/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Site4[/TD]
[TD][/TD]
[TD][/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]Site5[/TD]
[TD][/TD]
[TD][/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Site6[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]



Table2:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl98, width: 126"]IDENT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl98, width: 74"]Job[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl98, width: 113"]Project code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Mg[/TD]
[TD]OH[/TD]
[TD]Cu[/TD]
[TD]Cd[/TD]
[TD]Fe[/TD]
[TD]I[/TD]
[TD]Mn[/TD]
[/TR]
[TR]
[TD]UNITS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SCHEME[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site7[/TD]
[TD][/TD]
[TD][/TD]
[TD]44[/TD]
[TD]35[/TD]
[TD]63[/TD]
[TD]74[/TD]
[TD]74[/TD]
[TD]28[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]Site8[/TD]
[TD][/TD]
[TD][/TD]
[TD]53[/TD]
[TD]45[/TD]
[TD]65[/TD]
[TD]131[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Site9[/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]157[/TD]
[TD]138[/TD]
[TD]49[/TD]
[TD]20[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Site10[/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]Site11[/TD]
[TD][/TD]
[TD][/TD]
[TD]93[/TD]
[TD]30[/TD]
[TD]12[/TD]
[TD]23[/TD]
[TD]32[/TD]
[TD]43[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]Site12[/TD]
[TD][/TD]
[TD][/TD]
[TD]63[/TD]
[TD]47[/TD]
[TD]48[/TD]
[TD]93[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]14

[/TD]
[/TR]
</tbody>[/TABLE]




Result I want the mastertable to look like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl98, width: 126"]IDENT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="class: xl98, width: 74"]Job[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl98, width: 113"]Project code[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Mg[/TD]
[TD]OH[/TD]
[TD]Cu[/TD]
[TD]Cd[/TD]
[TD]Fe[/TD]
[TD]I[/TD]
[TD]Mn[/TD]
[TD]pH[/TD]
[TD]EC[/TD]
[TD]Alkalinity[/TD]
[TD]TSS[/TD]
[TD]NO2[/TD]
[TD]HARDNESS[/TD]
[/TR]
[TR]
[TD]UNITS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SCHEME[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site1[/TD]
[TD][/TD]
[TD][/TD]
[TD]44[/TD]
[TD]35[/TD]
[TD]63[/TD]
[TD]74[/TD]
[TD]74[/TD]
[TD]28[/TD]
[TD]91[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site2[/TD]
[TD][/TD]
[TD][/TD]
[TD]53[/TD]
[TD]45[/TD]
[TD]65[/TD]
[TD]131[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site3[/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]157[/TD]
[TD]138[/TD]
[TD]49[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site4[/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site5[/TD]
[TD][/TD]
[TD][/TD]
[TD]93[/TD]
[TD]30[/TD]
[TD]12[/TD]
[TD]23[/TD]
[TD]32[/TD]
[TD]43[/TD]
[TD]54[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site6[/TD]
[TD][/TD]
[TD][/TD]
[TD]63[/TD]
[TD]47[/TD]
[TD]48[/TD]
[TD]93[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]14

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Site8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Site9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Site10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]Site11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Site12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]


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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Baileyy said:
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.
Click to expand...

100 tables into one to view all of them in one big overview. sorry for wording it complexer than it needed.
 
Upvote 0
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.
 
Upvote 0
Have you tried using the consolidate function in the data tab. I found this worked for me when there are different headings.
 
Upvote 0
You must log in or register to reply here.

Similar threads

skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
536
skyh3ck
skyh3ck
Jimmypop
  • Solved
Formula Assistance
Replies
6
Views
393
Fluff
Fluff
M
  • Question Question
Help With Pivot - Combining Multiple Tables
Replies
5
Views
514
RoryA
RoryA
M
  • Question Question
This is challenging tricky combinations
Replies
6
Views
1K
motilulla
M
L
  • Question Question
Copy data from report header to each transaction.
Replies
3
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top