Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data from several spreadsheets into one.
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data from several spreadsheets into one.
read the help screens on the vlookup function, that's what you need
"Baz" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data from several spreadsheets into one.
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 "Baz" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data from several spreadsheets into one.
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. "Baz" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I consolidate data from several spreadsheets into one.
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. "taoggniklat" wrote: 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. "Baz" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i consolidate 13 spreadsheets onto one | Excel Worksheet Functions | |||
consolidate data | Excel Discussion (Misc queries) | |||
help to consolidate data | Excel Worksheet Functions | |||
Consolidate Spreadsheets | Excel Discussion (Misc queries) | |||
Consolidate multiple spreadsheets into a single workbook | Excel Discussion (Misc queries) |