Summarize using VBA Scripting Dictionary

[deba2020]

I have a data which contains 18 columns, I need to summarize the data based on the 1st 2 columns and in the summary there will be just 3 columns as shown in below table
Raw Data in Sheet1

DEPOT / CUSTOMER	SKU	DESCRIPTION	BUSINESS LINE	PRIMARY UOM	SECONDARY UOM	UNIT VOLUME	C1 (Y/N)	SO	RQ	ISO	ISO DATE	DLV	ORDERED	SHIPPED	PENDING	PICKED	WEIGHT
Customer_1	SKU1	Product 1	Business1	E	L	5	YES	149876	3764816	3542954	09-Nov-22	37627225	110	28	82	0	0
Customer_2	SKU2	Product 2	Business2	E	L	1	NO		3575499	3370748	06-Jun-22	37627226	684	516	168	0	0
Customer_3	SKU1	Product 3	Business3	E	L	2	YES	205575	3500505	3301785	05-Apr-22	37627227	1500	1320	180	0	0
Customer_1	SKU1	Product 4	Business4	E	L	2	YES	205575	3500507	3301787	05-Apr-22	37627228	1500	1468	32	0	0
Customer_5	SKU5	Product 5	Business5	E	L	2	YES	205575	3566757	3362678	30-May-22	37627229	600	352	248	0	0
Customer_3	SKU1	Product 6	Business6	E	L	10	YES	83586	3528928	3327850	28-Apr-22	37627230	1182	1161	21	0	0
Customer_1	SKU1	Product 7	Business7	E	L	4	YES	190970	3534232	3332754	02-May-22	37627231	152	120	32	0	0
Customer_2	SKU1	Product 8	Business8	E	L	18	YES	80458	3535595	3334065	03-May-22	37627232	950	931	19	0	0
Customer_3	SKU2	Product 9	Business9	E	L	1	NO		3544187	3341957	10-May-22	37627233	1800	1716	84	0	0
Customer_1	SKU2	Product 10	Business10	E	L	16	YES	131602	3516206	3316242	18-Apr-22	37627234	100	84	16	0	0
Customer_2	SKU2	Product 11	Business11	E	L	10	YES	78372	3539301	3337438	06-May-22	37627235	1400	1365	35	0	0
Customer_2	SKU2	Product 12	Business12	E	L	1	NO		3545288	3342976	11-May-22	37627236	828	624	204	0	0
Customer_13	SKU2	Product 13	Business13	E	L	10	NO		3545293	3342980	11-May-22	37627237	96	72	24	0	0
Customer_14	SKU3	Product 14	Business14	E	L	20	NO		3597923	3391123	25-Jun-22	37627238	270	250	20	0	0
Customer_15	SKU2	Product 15	Business15	E	L	14	NO		3611702	3403929	06-Jul-22	37627239	150	71	79	0	0

Need Summary in Sheet 2 as follows with these 3 columns (1st 2 columns and Sum of Column "Pending")

DEPOT / CUSTOMER	SKU	PENDING
Customer_1	SKU1	146
Customer_1	SKU2	16
Customer_13	SKU2	24
Customer_14	SKU3	20
Customer_15	SKU2	79
Customer_2	SKU1	19
Customer_2	SKU2	407
Customer_3	SKU1	201
Customer_3	SKU2	84
Customer_5	SKU5	248

 

[veyselemre]

Hi, alternative..

Sub test()
    Dim lst, i&, ky$, say&

    With Sheets("Sheet1")
        lst = .Range("A1:P" & .Cells(Rows.Count, 1).End(3).Row).Value
    End With

    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(lst)
            ky = lst(i, 1) & "|" & lst(i, 2)
            If Not .exists(ky) Then
                say = say + 1
                lst(say, 1) = lst(i, 1)
                lst(say, 2) = lst(i, 2)
                lst(say, 3) = lst(say, 16)
                .Item(ky) = say
            Else
                lst(.Item(ky), 3) = lst(.Item(ky), 3) + lst(say, 16)
            End If
        Next i
    End With

    With Sheets("Sheet2")
        .Cells.ClearContents
        .Range("A1").Resize(say, 3).Value = lst
    End With

End Sub