Merge two worksheet with same criteria
 

Hi,

Can somebody help me with this request. I need to have a macro to merge and
join the names of sheet 1 and 2 in a new sheet. If a name appears on both
sheets, each number on Sheet1 and Sheet2 with the same name must be indicated
in a separate column next to the name. If a name exists in sheet 1, it must
still be added to the new sheet with the corresponding number and vice versa
for sheet2.

I am new with VB code and I don't know how I can do it

Thanks
Catherine


Sheet1
Column A Column B
Name Number
1 a
2 b
3 c

Sheet2
Column A Column B
Name Number
1 c
2 d
4 e


NewWorksheet
Column A Column B Column C
Name Number Sheet1 Number Sheet2
1 a c
2 b d
3 c
4 e

Merge two worksheet with same criteria
 

The code below assumes there is one header row on each sheet

Sub makesummary()

Sheets("Sheet1").Copy _
after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Summary"
'copy sheet 1 to summary sheet
With Sheets("Summary")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
'add headers to summary sheet
.Range("B1") = "Sheet 1 Numbers"
.Range("C1") = "Sheet 2 Numbers"
End With

'merge sheet 2 into summary
With Sheets("Sheet2")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
Name = .Range("A" & RowCount)
Number = .Range("A" & RowCount)
With Sheets("Summary")
Set c = .Columns("A").Find(what:=Name, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("A" & NewRow) = Name
.Range("C" & NewRow) = Number
NewRow = NewRow + 1
Else
.Range("C" & c.Row) = Number
End If
End With
Next RowCount
End With

'sort by name

With Sheets("Summary")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = .Range("A2:C" & LastRow)
SortRange.Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Header:=xlNo
End With

End Sub


"Catherine" wrote:

Hi,

Can somebody help me with this request. I need to have a macro to merge and
join the names of sheet 1 and 2 in a new sheet. If a name appears on both
sheets, each number on Sheet1 and Sheet2 with the same name must be indicated
in a separate column next to the name. If a name exists in sheet 1, it must
still be added to the new sheet with the corresponding number and vice versa
for sheet2.

I am new with VB code and I don't know how I can do it

Thanks
Catherine


Sheet1
Column A Column B
Name Number
1 a
2 b
3 c

Sheet2
Column A Column B
Name Number
1 c
2 d
4 e


NewWorksheet
Column A Column B Column C
Name Number Sheet1 Number Sheet2
1 a c
2 b d
3 c
4 e

Merge two worksheet with same criteria
 

Hi,

The macro is working but it not giving me the proper data number for the
sheet 2, it's giving the name instead of the Number.

What I need to modify

Name Sheet1 Sheet2
1 a 1(it's supposed to be c)
2 b 2(it's supposed to be d)
3 c
4 4(it's suppsed to be e)

Many thanks for your help
Catherine

"Joel" wrote:

The code below assumes there is one header row on each sheet

Sub makesummary()

Sheets("Sheet1").Copy _
after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Summary"
'copy sheet 1 to summary sheet
With Sheets("Summary")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
'add headers to summary sheet
.Range("B1") = "Sheet 1 Numbers"
.Range("C1") = "Sheet 2 Numbers"
End With

'merge sheet 2 into summary
With Sheets("Sheet2")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
Name = .Range("A" & RowCount)
Number = .Range("A" & RowCount)
With Sheets("Summary")
Set c = .Columns("A").Find(what:=Name, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("A" & NewRow) = Name
.Range("C" & NewRow) = Number
NewRow = NewRow + 1
Else
.Range("C" & c.Row) = Number
End If
End With
Next RowCount
End With

'sort by name

With Sheets("Summary")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = .Range("A2:C" & LastRow)
SortRange.Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Header:=xlNo
End With

End Sub


"Catherine" wrote:

Hi,

Can somebody help me with this request. I need to have a macro to merge and
join the names of sheet 1 and 2 in a new sheet. If a name appears on both
sheets, each number on Sheet1 and Sheet2 with the same name must be indicated
in a separate column next to the name. If a name exists in sheet 1, it must
still be added to the new sheet with the corresponding number and vice versa
for sheet2.

I am new with VB code and I don't know how I can do it

Thanks
Catherine


Sheet1
Column A Column B
Name Number
1 a
2 b
3 c

Sheet2
Column A Column B
Name Number
1 c
2 d
4 e


NewWorksheet
Column A Column B Column C
Name Number Sheet1 Number Sheet2
1 a c
2 b d
3 c
4 e

Merge two worksheet with same criteria
 

Just modify the code to:

For RowCount = 2 To LastRow
Name = .Range("A" & RowCount)
Number = .Range("B" & RowCount)

regards
ramesh

*** Sent via Developersdex http://www.developersdex.com ***

Merge two worksheet with same criteria
 

Thank you, it's working now

"ramesh" wrote:



Just modify the code to:

For RowCount = 2 To LastRow
Name = .Range("A" & RowCount)
Number = .Range("B" & RowCount)

regards
ramesh

*** Sent via Developersdex http://www.developersdex.com ***