|
发表于 2013-7-11 10:50
|
显示全部楼层
本楼为最佳答案
- Sub 合并数据()
- Dim arr, arrResult
- Dim i As Long, j As Long, k As Long
- Dim strKey As String
- Dim lCount As Long, lPos As Long, arrPos
- Dim objDic As Object
- Dim strLast As String
- Set objDic = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1").CurrentRegion
- If Not IsArray(arr) Then
- MsgBox "数据不符合要求"
- Exit Sub
- End If
- ReDim arrResult(1 To UBound(arr), 1 To UBound(arr, 2))
- For j = LBound(arr, 2) To UBound(arr, 2)
- arrResult(1, j) = arr(1, j)
- Next
- lCount = 1
- k = UBound(arrResult, 2)
- For i = LBound(arr) + 1 To UBound(arr)
- strKey = arr(i, 1) & "#" & arr(i, 5) & "#" & arr(i, 6) & "#" & arr(i, 7) & "#" & arr(i, 8)
- If objDic.exists(strKey) Then
- lPos = objDic(strKey)
- arrResult(lPos, 9) = arrResult(lPos, 9) & " " & arr(i, 9)
- arrResult(lPos, 10) = arrResult(lPos, 10) + arr(i, 10)
- Else
- lCount = lCount + 1
- objDic.Add strKey, lCount
- lPos = lCount
- For j = LBound(arr, 2) To k
- arrResult(lPos, j) = arr(i, j)
- Next
- If arrResult(lPos, 7) = arrResult(lPos - 1, 7) And arrResult(lPos, 6) = arrResult(lPos - 1, 6) And arrResult(lPos, 5) = arrResult(lPos - 1, 5) Then
- arrResult(lPos, k) = ""
- End If
- End If
- Next
- 'With Sheet2
- '.UsedRange.ClearContents
- '.Range("a1").Resize(lCount, k).Value = arrResult
- 'end with
-
- Worksheets.Add
- Range("a1").Resize(lCount, k).Value = arrResult
-
- MsgBox "合并完成"
-
- End Sub
复制代码 |
|