Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Hi
I have a workbook with 2 worksheet Sheet 1: Product CountQty A 10 B 20 Sheet 2: Model SysQty A 10 C 30 Is there a way to use a function to consolidate two set of data from both worksheet into a new sheet to Product CountQty SysQty A 10 10 B 20 C 30 Thanks! kk |
#2
![]() |
|||
|
|||
![]()
With a formula just look in HELP index for VLOOKUP
-- Don Guillett SalesAid Software "kk" wrote in message ... Hi I have a workbook with 2 worksheet Sheet 1: Product CountQty A 10 B 20 Sheet 2: Model SysQty A 10 C 30 Is there a way to use a function to consolidate two set of data from both worksheet into a new sheet to Product CountQty SysQty A 10 10 B 20 C 30 Thanks! kk |
#3
![]() |
|||
|
|||
![]()
kk,
You could also automate it. Try the macro below. It will combine the databases from all the sheets in a workbook, based on the field names in row 1 and the key values in column A. Note that you would need to have the same value in cell A1 of both sheets: PRoduct and Model should be the same. HTH, Bernie MS Excel MVP Sub ConsolidateDatabasesMultiSheets() Dim BaseBook As Workbook Dim BaseSheet As Worksheet Dim myBook As Workbook Dim mySht As Worksheet Dim myCell As Range Dim myCell2 As Range Dim myColumn As Integer Dim myRow As Long Dim FirstCopy As Boolean FirstCopy = True With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With Set BaseSheet = Worksheets.Add ActiveSheet.Name = "Combined" For Each mySht In ActiveWorkbook.Worksheets If mySht.Name < BaseSheet.Name Then If FirstCopy Then mySht.Cells.Copy BaseSheet.Range("A1") FirstCopy = False GoTo NextSheet: End If mySht.Activate myRow = BaseSheet.UsedRange.Rows.Count + 1 For Each myCell In Intersect(Range("1:1"), _ ActiveSheet.UsedRange) If myCell.Value < "" Then If IsError(Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False)) Then With BaseSheet.Range("IV1").End(xlToLeft)(1, 2) ..Value = myCell.Value myColumn = .Column End With Else myColumn = Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False) End If For Each myCell2 In Intersect(Range("A2:A65536"), _ ActiveSheet.UsedRange) If IsError(Application.Match(myCell2.Value, _ BaseSheet.Range("A:A"), False)) Then With BaseSheet.Range("A65536").End(xlUp)(2) ..Value = myCell2.Value myRow = .Row End With Else myRow = Application.Match(myCell2.Value, _ BaseSheet.Range("A:A"), False) End If BaseSheet.Cells(myRow, myColumn).Value = _ Cells(myCell2.Row, myCell.Column).Value Next myCell2 End If Next myCell End If NextSheet: Next mySht ActiveWorkbook.SaveAs Application.GetSaveAsFilename("Consolidated.xls") With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "kk" wrote in message ... Hi I have a workbook with 2 worksheet Sheet 1: Product CountQty A 10 B 20 Sheet 2: Model SysQty A 10 C 30 Is there a way to use a function to consolidate two set of data from both worksheet into a new sheet to Product CountQty SysQty A 10 10 B 20 C 30 Thanks! kk |
#4
![]() |
|||
|
|||
![]()
Hello Don
Beside vlookup, is there any other way to do it? Each spreadsheet containing thousand of lines and duplicate product. I need to find out what product is missing in sheet 1 compare to sheet 2 and vice, filter out the duplicate, sum the quantity and put in into the separate sheet. Thanks "Don Guillett" wrote in message ... With a formula just look in HELP index for VLOOKUP -- Don Guillett SalesAid Software "kk" wrote in message ... Hi I have a workbook with 2 worksheet Sheet 1: Product CountQty A 10 B 20 Sheet 2: Model SysQty A 10 C 30 Is there a way to use a function to consolidate two set of data from both worksheet into a new sheet to Product CountQty SysQty A 10 10 B 20 C 30 Thanks! kk |
#6
![]() |
|||
|
|||
![]()
Hi Don,
Thanks! "Don Guillett" wrote in message ... try Sub consolidate() For i = 1 To 2 With Sheets(i) On Error Resume Next For Each c In Sheets("sheet3").Range("a2:a15") c.Offset(0, i) = .Range("a2:a3").Find(c).Offset(0, 1) Next End With Next End Sub or more crudely depending on your structure Sub consolidate1() With Sheets("sheet1") On Error Resume Next For Each c In Sheets("sheet3").Range("a2:a15") c.Offset(0, 1) = .Range("a2:a3").Find(c).Offset(0, 1) Next End With With Sheets("sheet2") On Error Resume Next For Each c In Sheets("sheet3").Range("a2:a15") c.Offset(0, 2) = .Range("a2:a3").Find(c).Offset(0, 1) Next End With End Sub -- Don Guillett SalesAid Software "kk" wrote in message ... Hello Don Beside vlookup, is there any other way to do it? Each spreadsheet containing thousand of lines and duplicate product. I need to find out what product is missing in sheet 1 compare to sheet 2 and vice, filter out the duplicate, sum the quantity and put in into the separate sheet. Thanks "Don Guillett" wrote in message ... With a formula just look in HELP index for VLOOKUP -- Don Guillett SalesAid Software "kk" wrote in message ... Hi I have a workbook with 2 worksheet Sheet 1: Product CountQty A 10 B 20 Sheet 2: Model SysQty A 10 C 30 Is there a way to use a function to consolidate two set of data from both worksheet into a new sheet to Product CountQty SysQty A 10 10 B 20 C 30 Thanks! kk |
#7
![]() |
|||
|
|||
![]()
Hi Don,
Thanks! "Don Guillett" wrote in message ... try Sub consolidate() For i = 1 To 2 With Sheets(i) On Error Resume Next For Each c In Sheets("sheet3").Range("a2:a15") c.Offset(0, i) = .Range("a2:a3").Find(c).Offset(0, 1) Next End With Next End Sub or more crudely depending on your structure Sub consolidate1() With Sheets("sheet1") On Error Resume Next For Each c In Sheets("sheet3").Range("a2:a15") c.Offset(0, 1) = .Range("a2:a3").Find(c).Offset(0, 1) Next End With With Sheets("sheet2") On Error Resume Next For Each c In Sheets("sheet3").Range("a2:a15") c.Offset(0, 2) = .Range("a2:a3").Find(c).Offset(0, 1) Next End With End Sub -- Don Guillett SalesAid Software "kk" wrote in message ... Hello Don Beside vlookup, is there any other way to do it? Each spreadsheet containing thousand of lines and duplicate product. I need to find out what product is missing in sheet 1 compare to sheet 2 and vice, filter out the duplicate, sum the quantity and put in into the separate sheet. Thanks "Don Guillett" wrote in message ... With a formula just look in HELP index for VLOOKUP -- Don Guillett SalesAid Software "kk" wrote in message ... Hi I have a workbook with 2 worksheet Sheet 1: Product CountQty A 10 B 20 Sheet 2: Model SysQty A 10 C 30 Is there a way to use a function to consolidate two set of data from both worksheet into a new sheet to Product CountQty SysQty A 10 10 B 20 C 30 Thanks! kk |
#8
![]() |
|||
|
|||
![]()
Hi Bernie
Thanks for your reply. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... kk, You could also automate it. Try the macro below. It will combine the databases from all the sheets in a workbook, based on the field names in row 1 and the key values in column A. Note that you would need to have the same value in cell A1 of both sheets: PRoduct and Model should be the same. HTH, Bernie MS Excel MVP Sub ConsolidateDatabasesMultiSheets() Dim BaseBook As Workbook Dim BaseSheet As Worksheet Dim myBook As Workbook Dim mySht As Worksheet Dim myCell As Range Dim myCell2 As Range Dim myColumn As Integer Dim myRow As Long Dim FirstCopy As Boolean FirstCopy = True With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With Set BaseSheet = Worksheets.Add ActiveSheet.Name = "Combined" For Each mySht In ActiveWorkbook.Worksheets If mySht.Name < BaseSheet.Name Then If FirstCopy Then mySht.Cells.Copy BaseSheet.Range("A1") FirstCopy = False GoTo NextSheet: End If mySht.Activate myRow = BaseSheet.UsedRange.Rows.Count + 1 For Each myCell In Intersect(Range("1:1"), _ ActiveSheet.UsedRange) If myCell.Value < "" Then If IsError(Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False)) Then With BaseSheet.Range("IV1").End(xlToLeft)(1, 2) .Value = myCell.Value myColumn = .Column End With Else myColumn = Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False) End If For Each myCell2 In Intersect(Range("A2:A65536"), _ ActiveSheet.UsedRange) If IsError(Application.Match(myCell2.Value, _ BaseSheet.Range("A:A"), False)) Then With BaseSheet.Range("A65536").End(xlUp)(2) .Value = myCell2.Value myRow = .Row End With Else myRow = Application.Match(myCell2.Value, _ BaseSheet.Range("A:A"), False) End If BaseSheet.Cells(myRow, myColumn).Value = _ Cells(myCell2.Row, myCell.Column).Value Next myCell2 End If Next myCell End If NextSheet: Next mySht ActiveWorkbook.SaveAs Application.GetSaveAsFilename("Consolidated.xls") With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "kk" wrote in message ... Hi I have a workbook with 2 worksheet Sheet 1: Product CountQty A 10 B 20 Sheet 2: Model SysQty A 10 C 30 Is there a way to use a function to consolidate two set of data from both worksheet into a new sheet to Product CountQty SysQty A 10 10 B 20 C 30 Thanks! kk |
#9
![]() |
|||
|
|||
![]()
glad to help
-- Don Guillett SalesAid Software "kk" wrote in message ... Hi Don, Thanks! "Don Guillett" wrote in message ... try Sub consolidate() For i = 1 To 2 With Sheets(i) On Error Resume Next For Each c In Sheets("sheet3").Range("a2:a15") c.Offset(0, i) = .Range("a2:a3").Find(c).Offset(0, 1) Next End With Next End Sub or more crudely depending on your structure Sub consolidate1() With Sheets("sheet1") On Error Resume Next For Each c In Sheets("sheet3").Range("a2:a15") c.Offset(0, 1) = .Range("a2:a3").Find(c).Offset(0, 1) Next End With With Sheets("sheet2") On Error Resume Next For Each c In Sheets("sheet3").Range("a2:a15") c.Offset(0, 2) = .Range("a2:a3").Find(c).Offset(0, 1) Next End With End Sub -- Don Guillett SalesAid Software "kk" wrote in message ... Hello Don Beside vlookup, is there any other way to do it? Each spreadsheet containing thousand of lines and duplicate product. I need to find out what product is missing in sheet 1 compare to sheet 2 and vice, filter out the duplicate, sum the quantity and put in into the separate sheet. Thanks "Don Guillett" wrote in message ... With a formula just look in HELP index for VLOOKUP -- Don Guillett SalesAid Software "kk" wrote in message ... Hi I have a workbook with 2 worksheet Sheet 1: Product CountQty A 10 B 20 Sheet 2: Model SysQty A 10 C 30 Is there a way to use a function to consolidate two set of data from both worksheet into a new sheet to Product CountQty SysQty A 10 10 B 20 C 30 Thanks! kk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Multiple Consolidation Ranges - Debra Dalgleish | Excel Discussion (Misc queries) | |||
Multiple Consolidation Ranges | Excel Discussion (Misc queries) | |||
How to integrate cells from different .xls into one consolidation | Excel Discussion (Misc queries) | |||
Product Function in Pivot Tables from Multiple Consolidation Range | Excel Worksheet Functions |