Public Sub xx()
Dim ss, s1, s2 As String
Dim bh, eh, i As Integer
'为简化程序,使用之前须对“名称一”所在列进行排序
'bh,eh 分别为“名称一”的起始行号
bh = 1
eh = 3
ss = Sheet1.Cells(bh, 2)
s1 = ""
s2 = ""
For i = bh To eh
s1 = s1 & "," & Sheet1.Cells(i, 1)
s2 = s2 & "," & Sheet1.Cells(i, 3)
Next
ss = ss & s1 & s2
'将得到的结果填写到sheet2
Sheet2.Cells(1, 1) = ss
'bh,eh 分别为“名称二”的起始行号,以后类推
bh = 4
eh = 5
ss = Sheet1.Cells(bh, 2)
s1 = ""
s2 = ""
For i = bh To eh
s1 = s1 & "," & Sheet1.Cells(i, 1)
s2 = s2 & "," & Sheet1.Cells(i, 3)
Next
ss = ss & s1 & s2
'将得到的结果填写到sheet2
Sheet2.Cells(2, 1) = ss
End Sub