Modify This To Properly ombine col C

G

gjfeng

this is linked to: http://www.mrexcel.com/forum/showthread.php?t=325848&page=2
Data in col A, Col B combines correctly, so is the rest. I need to
modify the code below further to combine Col C.

Dim a, b(), c(), i As Long, ii As Integer, n As Long, z As String
With Sheets("grandTotal").Range("a1").CurrentRegion
a = .Value
If .Column <> 1 Then
a = .Offset(, -1).Resize(, .Columns.Count).Value
End If

ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
For i = 2 To UBound(a, 2) 'affects column
b(1, i) = a(1, i): b(2, i) = a(2, i)

Next
n = 1 ' affect the rows, correct if 1
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 2 To UBound(a, 1) ' affects how data is
shown, correct if i=2
z = a(i, 1) & ";" & a(i, 2)
If Not .exists(z) Then
n = n + 1: b(n, 1) = a(i, 1): b(n, 2) = a(i, 2): .Add
z, n
End If
For ii = 3 To UBound(a, 2) ' affects combined data,
correct if ii=3
b(.Item(z), ii) = b(.Item(z), ii) + a(i, ii)
Next
Next
End With
'With Sheets("grandTotal").Range("a1").CurrentRegion.Cells(1)
With .Offset(.Rows.Count + 1, IIf(.Column <> 1, -1, 0)).Resize(1,
1)
.CurrentRegion.ClearContents
.Resize(n, UBound(b, 2)).Value = b
End With
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top