I have two worksheets with data related to specific people. The people
contained in the sheets are similar, but there are some differences. I want
to create a single worksheet that adds the data from the two sheets together
so that data for each person is combined into a single row.

How?
--
Thanks

I'm having trouble using the help you've provided. I'm very thankful, but I
also rather ignorant of programming. What exactly do I do with the code
you've provided?
--
Thanks


"Bill Pfister" wrote:


Public Sub CombineSheets()
Dim wkb As Workbook
Dim rngA As Range
Dim rngB As Range
Dim rngC As Range
Dim colItems As New Collection
Dim i As Long

Set wkb = ThisWorkbook

Set rngA = wkb.Worksheets("shtA").Range("A1")
Set rngB = wkb.Worksheets("shtB").Range("A1")
Set rngC = wkb.Worksheets("shtC").Range("A1")

Call CombineSheetsIndividual(colItems, rngA, "ValueA")
Call CombineSheetsIndividual(colItems, rngB, "ValueB")

rngC.Parent.Range(rngC.Row & ":" & _
rngC.SpecialCells(xlCellTypeLastCell).Row).Delete
Set rngC = wkb.Worksheets("shtC").Range("A1")

For i = 1 To colItems.Count
rngC.Offset(i - 1, 0).Value = colItems.Item(i).Item("Name")
rngC.Offset(i - 1, 1).Value = colItems.Item(i).Item("ValueA")
rngC.Offset(i - 1, 2).Value = colItems.Item(i).Item("ValueB")
Next i
End Sub



Public Sub CombineSheetsIndividual( _
colItems As Collection, rng As Range, strValueKey As String)
Dim strKey As String
Dim dblValue As Double
Dim lngCount As Long
Dim i As Long

lngCount = rng.SpecialCells(xlCellTypeLastCell).Row

For i = 0 To lngCount - 1
strKey = rng.Offset(i, 0).Value

If (Len(strKey) 0) Then
If (Not (CollectionKeyExists(colItems, strKey))) Then
Call colItems.Add(New Collection, strKey)
Call colItems.Item(strKey).Add(strKey, "Name")
Call colItems.Item(strKey).Add("", "ValueA")
Call colItems.Item(strKey).Add("", "ValueB")
End If

If (Len(rng.Offset(i, 1).Value) 0) Then
dblValue = rng.Offset(i, 1).Value
Call colItems.Item(strKey).Remove(strValueKey)
Call colItems.Item(strKey).Add(dblValue, strValueKey)
End If
End If
Next i
End Sub




Public Function CollectionKeyExists( _
col As Collection, strKey As String) As Boolean
Dim varCheck As Variant

On Error GoTo ErrHandler

Set varCheck = col.Item(strKey)

CollectionKeyExists = True

Exit Function
ErrHandler:
CollectionKeyExists = False
End Function





"bearcomp" wrote:

After re-reading my quesiton, I realized that I wasn't clear. What I want
to do is create a third worksheet that contains cells from sheetA and sheetB.
SheetC would be a single sheet that would consolidate both sheetA and sheetB
so that all of the data would be in a single sheet.

For example:

Sheet A says Sheet B says
Unique list: Data 1 Unique list Data 2
A 1 A
2
B B
3
C 4 C

_______________________________________________
I want Sheet C to say
Unique list: Data 1 Data 2
A 1 2
B 3
C 4

My sheets contain many columns that I want to move simultaneously. It's a
complicated copy and paste function that I'm looking for.

Thanks


"Bill Pfister" wrote:


There are several methods to do this formulaically without VBA. Here is
one: First thing is to get a unique list of the names from the two
worksheets. Then sum SumIf functions, one for each worksheet you are
referencing, with the unique list as the criteria.

For example, SheetA contains one list and SheetB contains another. SheetSum
is your consolidation sheet. Assume all sheets have similar layout (names in
column A, values in columns B.

SheetSum!B1 formula: €œ=sumif( SheetA!$A$1:$A$100, A1, SheetA!B$1:B$100 ) +
sumif( SheetB!$A$1:$A$100, A1, SheetB!B$1:B$100 )€�


"bearcomp" wrote:

I have two worksheets with data related to specific people. The people
contained in the sheets are similar, but there are some differences. I want
to create a single worksheet that adds the data from the two sheets together
so that data for each person is combined into a single row.

How?
--
Thanks