Merge two worksheet with same criteria

[Catherine]

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

 

[Joel]

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]

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

 

[ramesh]

Just modify the code to:

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

regards
ramesh

 

[Catherine]

Thank you, it's working now