merging large databases

I have 6 large databases I want to merge together into one even bigger one.
They are on separate sheets with headers:

ID tag     value1   value2   value3

ID tags are always in the same format. Values vary from sheet to sheet. each sheet will provide 2-5 values, so my finished sheet will be:

ID tag     value1   value2   ...  value18 (or so)

This would be easy enough just by sorting and copying columns, except:

1) The list of IDtags, while generally similar, are not identical. So sheet1 has
A
B
C
F

but sheet2 has
A
C
E
G

Ultimately, my final product will be ABCDEFG, although some of the columns will be filled with zero values, looking something like this

A    3    4    5
B    6    6     8  
C    4          4         4
D                5         5
E     5                    9

2) Normally I'd use vlookup for this, except each of my sheets has 40,000 lines, and multiplied over 5 sheets means I have to do 20 (or whatever) number of lookups to catch all the unique values. In our experience here, these can take 10-15 minutes to pull in all the data from one column of vlookup, and then we do a paste special to get rid of the formulas or the file will take forever every time we need it.

So I guess my questions are:
1) how do I first create a master list that analyzes Column A of all sheets to create an index if IDtags
2) then put in 15-20 vlookup columns in to populate my master sheet
3) get this into a macro since I know it's going to be real slow, and I'll probably need to run it overnight.

I think it's mostly #1 that I don't know how to do

ThanksStart Free Trial