How to sum values and remove duplicates rows

[Anatoliy1992]

Hi All,

I'm trying to remove duplicates and sum values. I have table which is looks like that.

item number	client	Date	Quantity
167​	C10000038	28/01/2022 00:00​	3​
600831​	C10008630	27/01/2022 00:00​	1​
600831​	C10008630	27/01/2022 00:00​	48​
600827​	C10008630	28/01/2022 00:00​	1​
950114​	C10008630	28/01/2022 00:00​	3​
1357​	C10008630	28/01/2022 00:00​	2​
600827​	C10008630	28/01/2022 00:00​	1​
950115​	C10008630	27/01/2022 00:00​	1​
600831​	C10008630	27/01/2022 00:00​	1​

please see code what i use below, it doesn't work properly, I have already blow my mind how to solve it.

Sub CombineDupes()
    
    Dim x       As Long
    Dim r       As Long
    Dim arr()   As Variant
    Dim dic     As Object
    Const DELIM As String = "|"
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    x = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Cells(1, 1).Resize(x, 4).Value
    
    For x = LBound(arr, 1) + 1 To UBound(arr, 1)
        If dic.exists(arr(x, 1)) Then
            arr(x, 4) = arr(x, 4) + CDbl(Split(dic(arr(x, 1)), DELIM)(2))
        Else
            dic(arr(x, 1)) = arr(x, 2) & DELIM & arr(x, 3) & DELIM & arr(x, 4)
        End If
        dic(arr(x, 1)) = arr(x, 2) & DELIM & arr(x, 3) & DELIM & arr(x, 4)
    Next x
    
    r = UBound(arr, 1) + 2
    
    Application.ScreenUpdating = False
    
    Cells(r, 1).Resize(, 4).Value = Cells(1, 1).Resize(, 4).Value
    r = r + 1
    
     For x = 0 To dic.Count - 1
        Cells(r + x, 1).Value = dic.keys()(x)
        Cells(r + x, 2).Resize(, 3).Value = Split(dic.items()(x), DELIM)
        Cells(r + x, 4).Value = CDbl(Cells(r, 4).Value)
    Next x
    
    Application.ScreenUpdating = True
    
    Erase arr
    Set dic = Nothing
    
End Sub

[#2]

@Anatoliy1992 Can't help you with the VBA code but perhaps you want to consider using Power Query in stead. See attached.

[#3]

@Anatoliy1992 Hi

Yet another alternative is doing it on the worksheet using SUMIFS and, assuming 365, UNIQUE.

Two versions;

G2 =UNIQUE(A2:C10) results in the unique combinations of number client and date.

M2 =UNIQUE(A2:A10) results in only unique item numbers and could be functional if an item number should have just one combination of clients and dates (I doubt it in this case but the it would be any version of lookup, preferrably xlookup, in n2 and o2)

What could be tricky is addressing item number, client and date individually in the three columns wide spill area g2#, but one way is using the operator space, resulting in a range common for two ranges. Space is like @ but not for only one cell*.

=SUMIFS(D:D,A:A,G2# G:G,B:B,G2# H:H,C:C,G2# I:I)

G2# is in this case same as G2:i7

G2:i7 H:H equals H2:H7

The sumifs above is like

sum D:D if A:A equals G2:G7 and B:B equals H2:H7 and C:C equals i2:i7

A difference against Power Query is that the result is updated every time data is changed.

If input is another file, eg a csv export from another system, Power Query is handy to get the transformation done when the file is loaded and thus calculated only once per import - not every time something is changed on the worksheet.

* @ can be seen as a function and as such could have been called and displayed as SINGLE()