VBA code - need little help

[Ismo]

I've found this code, and it almost does perfectly what I'm after. My only problem is, that this code sums more cells than I wanted.

My raw data has 7 columns (A-G). I need to check the duplicates based on multiple cells (A-H) and sums up G.

This code does the job but it also sums up every data in D,E,F,G (I only need a summary in G).

I've fairly new to VBA and tried to play with the numbers, but I've only managed to clear the values in D,E,F,G.

Sub AB()
Dim a, i As Long, ii As Integer, b(), n As Long, z As String
With ActiveSheet.Range("a1").CurrentRegion
    a = .Value
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    For i = 1 To UBound(a, 2): b(1, i) = a(1, i): Next
    n = 1
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(a, 1)
            z = a(i, 1) & ";" & a(i, 2) & ";" & a(i, 3) & ";" & a(i, 4) & ";" & a(i, 5) & ";" & a(i, 6)
            If Not .exists(z) Then
                n = n + 1: .Add z, n
                For ii = 1 To 3: b(n, ii) = a(i, ii): Next
            End If
            For ii = 4 To UBound(a, 2)
                b(.Item(z), ii) = b(.Item(z), ii) + a(i, ii)
            Next
        Next
    End With
    .Value = b
End With
End Sub

 

[Ismo]

After the code

 

[Ismo]

I've just seen I posted the same screenshot.

So the data looks like this before the code:

 

[mirabeau]

Ismo,

I can't test the code on your data, because of the way you post it, and copying it down manually from your pictures is not something I want to do. I don't have handy a good OCR program, and I don't think the definition of your pics is good enough for that anyway.

And I'm still unclear just what you want your "before" and "after" situations to be, particularly regarding your column G.

Could you please re-explain just what you want to be done.

 

[Ismo]

Ismo,

I can't test the code on your data, because of the way you post it, and copying it down manually from your pictures is not something I want to do. I don't have handy a good OCR program, and I don't think the definition of your pics is good enough for that anyway.

And I'm still unclear just what you want your "before" and "after" situations to be, particularly regarding your column G.

Could you please re-explain just what you want to be done.

Sorry. I've uploaded a sample workbook, if you could please have a look.

http://www.sendspace.com/file/xiklkc

Mainly, in column G there are the quantities which I have to sum up. Your code does the work almost perfectly, just it only adds one at a time for every identical row and ignores the quantity (which is not always just 1).

I really do appreaciate your help, thank you!

 

[Ismo]

I've tweaked your code a bit and this is what I'm after. I've changed this part:

For j = 1 To 6
b(d(u), j) = a(i, j)
Next j
b(d(u), 7) = 1
Else
b(d(u), 7) = b(d(u), 7) + 1
End If

to this

For j = 1 To 6
b(d(u), j) = a(i, j)
Next j
b(d(u), 7) = Range("G" & i)
Else
b(d(u), 7) = b(d(u), 7) + Range("G" & i)
End If


I will test it but I'm pretty sure that is what I was after. Thanks again your help!