How do I consolidate data from several spreadsheets into one.

[Baz]

I have several spreadsheets containing data that I would like to consolidate
into one spreadsheet. Each of the spreadsheets have a common Product Code
field column but all the other field columns are different. Obviously I can
cut and paste the data into one spreadsheet but that will leave me with
several rows of data for the same Product Code. Note also that not all the
Product Codes are listed on all the spreadsheets, I do however have one
master spreadsheet with all these Product Codes. This is what I want to see
and for example.
eg
Product Code Description 1 Description 2 Cost Trade Price

Can anyone help?

 

[dlw]

read the help screens on the vlookup function, that's what you need

 

[Joel]

Try this code


Sub MakeMaster()

Set MSht = Sheets("Master")
'Get LastColumn on master sheet
LastCol = MSht.Cells(1, Columns.Count).End(xlToLeft).Column
If LastCol = 1 Then
NewCol = 2
Else
NewCol = LastCol + 1
End If

For Each sht In Sheets
If UCase(sht.Name) <> "MASTER" Then
'start at row 2 for each sheet
RowCount = 2
LastCol = sht.Cells(1, Columns.Count).End(xlToLeft).Column

'Loop through every row of sheet until blank cell is found in COL A
Do While sht.Range("A" & RowCount) <> ""
ProductCode = sht.Range("A" & RowCount)

'Find Product code in Master Sheet
With MSht
'Search column A in Master sheet for Product Code
Set c = .Columns("A").Find(what:=ProductCode, _
LookIn:=xlvlaues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Could not find Product Code : " & ProductCode)
Stop
Else
ProductRow = c.Row
End If
End With

'Loop through every column on sht
For ColCount = 2 To LastCol
'Get Column Header from Row 1
Header = sht.Cells(1, ColCount)
'Search for Column Header on Master Sheet
Set c = MSht.Rows(1).Find(what:=Header, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
'Add new column header to master sht
MSht.Cells(1, NewCol) = Header
MSht.Cells(ProductRow, NewCol) = sht.Cells(RowCount, ColCount)
NewCol = NewCol + 1
Else
'move data from current sheet to master sheet
MSht.Cells(ProductRow, c.col) = sht.Cells(RowCount, ColCount)
End If

Next ColCount

RowCount = RowCount + 1
Loop

End If
Next sht


End Sub

 

[taoggniklat]

Hi,

I would like to know if there is a way to do the reverse of what the OP is
asking. I want to take data on one spreadsheet and separate it out into
multiple worksheets or documents.

I am new to coding in excel and have limited knowledge of macros and vbasic.
I am using Excel 2003.

Thank you for any help.

 

[Joel]

The code would be very similar. You would start with a summary sheet and
manuall create all the other sheets. The new sheet you would put the Header
Names of the columns You would want on each sheet.

It would probably easier to just make a copy of the Summary sheet and delete
the columns you didn't want manually than writing a macro.