|
如有9列,最后一列不一样
ABCDEFGH
……………01
……………02
……………03
转换成
……………01,02,03
将相同行合并,最后一列不同的,加列,往后追加
- Sub MyMerge()
- Dim arr
- Dim d As Object
- Dim shtR As Worksheet
- Dim shtO As Worksheet
- Dim i As Long
- Dim rowN As Long
- Dim colN As Long
- Const Sep As String = "[_=dp23"
- Dim sKey As String
- Dim lCount As Long
- Dim arrTmp
- Set shtO = ActiveSheet
- With shtO
- arr = .Range("A2:H" & Sheet2.Range("A" & .Rows.Count).End(xlUp).Row).Value
- End With
- Set d = CreateObject("Scripting.Dictionary")
- Set shtR = Worksheets.Add
- shtR.Range("A1:H1").Value = shtO.Range("A1:H1").Value
- lCount = 1
- For i = 1 To UBound(arr)
- sKey = arr(i, 1) & Sep & arr(i, 2) & Sep & arr(i, 3) & Sep & arr(i, 4) & _
- arr(i, 5) & Sep & arr(i, 6) & Sep & arr(i, 7)
- If d.exists(sKey) Then
- arrTmp = Split(d(sKey), Sep)
- rowN = arrTmp(0)
- colN = arrTmp(1)
- Else
- colN = 8
- lCount = lCount + 1
- rowN = lCount
- Application.Intersect(shtR.Range("A:H"), shtR.Rows(rowN)).Value = _
- Application.Intersect(shtO.Range("A:H"), shtO.Rows(i + 1)).Value
- End If
- shtR.Cells(rowN, colN).Value = arr(i, 8)
- d(sKey) = rowN & Sep & colN + 1
- Next i
- End Sub
复制代码
|
|