Consolidation
 

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

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

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

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

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

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

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

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

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