VBA Excel 2010 - Dictionary with Array Suppress duplicates plus sum amount column

Hello

I have a Excel sheet containing a key (column A) and some columns (fixed value or amount).

I want the macro to suppress duplicates (keys) and at the same time sum amount for duplicates

Ex.
CCSA07194EUR M03/2014 EUR I 201403 CCSA07 -1033985
CCSA07220EUR M03/2014 EUR I 201403 CCSA07 -37840,17
CCSA07220EUR M03/2014 EUR I 201403 CCSA07 -37840,17
CCSA07220EUR M03/2014 EUR I 201403 CCSA07 -37840,17

expected results
CCSA07194EUR M03/2014 EUR I 201403 CCSA07 -1033985
CCSA07220EUR M03/2014 EUR I 201403 CCSA07 -113520,51 (sum of : -37840,17 x 3)


Due to number of lines (30.000), I tried to use the dictionary with function in Excel.

I created 2 dictionaries (one containing fixed values), another one for amount.

When transposing the value, column A gets the key (ok) but error message therefore I don't have any values for column B/C the values and column D not the sumed amounts

Attached the Excel file (not the 30.000 lines, just a test case with a couple of lines) including the macro.
Thanks in advance for any idea how to solve this.

Try this

Change 15 to # of columns that you want it to the result.

Please Login or Register  to view this content.

THanks to both of you. I finally found out with dictionary w/o looping:

Sub doublons()
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
Dim i As Long

For Each c In Range("A1:A16")
d1(c.Value) = Array(c.Offset(, 1), c.Offset(, 2))
d2(c.Value) = d2(c.Value) + c.Offset(, 7)
Next c
[a20].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
[b20].Resize(d1.Count, 1) = Application.Transpose(Application.Index(d1.items, 1, 1)) 'would like to take the 1st value of Array c.offset(,1)
[c20].Resize(d1.Count, 1) = Application.Transpose(Application.Index(d1.items, 1, 2)) 'would like to take the 2nd value of Array c.offset(,2)
[d20].Resize(d2.Count, 1) = Application.Transpose(d2.items)
End Sub

Strangely the code is working is my test workbook but when I try to replicate the code in the production workbook,
I received a message error 13 (type incompatibility)
... any idea why ?(I have attached the solution that is working out just fine).

Originally Posted by ffasan

THanks to both of you. I finally found out with dictionary w/o looping:

Please Login or Register  to view this content.

W/O loop?

What is your definition about "Loop"?

Loop or For/Next. I was looking for avoiding one of the two when writing the results (not selecting).

Let us know if you find the way to solve it w/o loop.

Good luck.