Merging Question

[Guest]

I'm not really sure if this is possible but I thought maybe someone could
help. I have information from 12 months in 12 different spreadsheets. There
is a unique identifer for each row... I want to merge the unique identifier
and 4 other columns into one sheet. Each month will be in a different column
with the date at the top. Is there a way to merge the unique identifier so
that the same product information will be in the same row? I can start by
copying all the information into one sheet from the 12 different sheets.. but
I'm not familiar with the merge feature. Any will be greatly appreciated!
Thanks in advance!

 

[Guest]

I don't think there is a merge feature. There is a consolidate feature under
the data menu, but I don't think it would do what you describe.

If you got everything onto one sheet you could probably achieve your purpose
by then creating a pivot table (again, under the data menu).


Another way would be to put a series of vlookup functions in the approriate
columns of the consolidation sheet and have them "lookup" the values in the
source sheets against a list of these unique identifier numbers in column A.

 

[Guest]

Oh ok. How do you type a vlookup formula directly into a cell with code?

 

[Guest]

Range("D7").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],RC[4]:R[18]C[5],2,FALSE)"

This will lookup the value in cell A7 in the table H7 to I25

 

[Guest]

Sub AddFormulas()
With Worksheets("Summary")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
rng.Offset(0, 1).Resize(, 5).Formula = _
"=Vlookup($A2,January!$A$1:$G$200,column(),False)"
rng.Offset(0, 6).Resize(, 5).Formula = _
"=Vlookup($A2,February!$A$1:$G$200,column()-5,False)"

End Sub

worked for me. Extend it to include all months and modify it to match your
data.

It assumes the unique identifier is is in column A of each sheet.