Add values in rows based on matching values in several columns

[Nanashi7562]

Hello and Good Day! This is my first post so please be gentle with me.
I have a data set where I have several rows of data and I need to copy the data to another sheet. Please refer to the sample sheet below.

I need to transfer the following data to another workbook using VBA. However, when I transfer it to the other workbook, if the columns for ID, NAME, CATEGORY and LOCATION should match,
the values in the columns Q1 to Q4 should be added. Please refer below.

Do you guys have any idea or suggestion on how to do this ? Any tips will be appreciated. Thank you.

 

[Kerryx]

Very quickly done using a pivot table

Book1
	A	B	C	D	E	F	G	H
2	ID	Name	Cat	Loc	Q1	Q2	Q3	Q4
3	1111	AZN	A	NYC	10	0	2	1
4	2234	BBn	B	LA	5	2	0	12
5	1234	ART	C	DC	3	3	3	3
6	1111	AZN	A	NYC	0	2	2	6
7	2234	BBN	B	LA	1	6	0	2
8	4456	RAT	D	CAN	8	8	8	8
9	2234	BBN	C	LA	1	6	0	2
10
11
12					Values
13	ID	Name	Cat	Loc	Sum of Q1	Sum of Q2	Sum of Q3	Sum of Q4
14	1111	AZN	A	NYC	10	2	4	7
15	1234	ART	C	DC	3	3	3	3
16	2234	BBn	B	LA	6	8	0	14
17			C	LA	1	6	0	2
18	4456	RAT	D	CAN	8	8	8	8
Sheet1

 

[mumps]

Assuming your data starts in row 3 of each sheet, try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, i As Long, dic As Object, val As String
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    v1 = srcWS.Range("A3").CurrentRegion.Value
    v2 = desWS.Range("A3").CurrentRegion.Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        val = v1(i, 1) & "|" & v1(i, 2) & "|" & v1(i, 3) & "|" & v1(i, 4)
        If Not dic.exists(val) Then
            dic.Add val, v1(i, 5) & "|" & v1(i, 6) & "|" & v1(i, 7) & "|" & v1(i, 8)
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        val = v2(i, 1) & "|" & v2(i, 2) & "|" & v2(i, 3) & "|" & v2(i, 4)
        If dic.exists(val) Then
            desWS.Range("E" & i + 2).Resize(, 4).Value = Array(Split(dic(val), "|")(0), Split(dic(val), "|")(1), Split(dic(val), "|")(2), Split(dic(val), "|")(3))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Change the sheet names (in red) to suit your needs.

 

[Nanashi7562]

Very quickly done using a pivot table

Book1
	A	B	C	D	E	F	G	H
2	ID	Name	Cat	Loc	Q1	Q2	Q3	Q4
3	1111	AZN	A	NYC	10	0	2	1
4	2234	BBn	B	LA	5	2	0	12
5	1234	ART	C	DC	3	3	3	3
6	1111	AZN	A	NYC	0	2	2	6
7	2234	BBN	B	LA	1	6	0	2
8	4456	RAT	D	CAN	8	8	8	8
9	2234	BBN	C	LA	1	6	0	2
10
11
12					Values
13	ID	Name	Cat	Loc	Sum of Q1	Sum of Q2	Sum of Q3	Sum of Q4
14	1111	AZN	A	NYC	10	2	4	7
15	1234	ART	C	DC	3	3	3	3
16	2234	BBn	B	LA	6	8	0	14
17			C	LA	1	6	0	2
18	4456	RAT	D	CAN	8	8	8	8
Sheet1

Thank you for the suggestion but unfortunately this needs to be implemented via VBA. We did originally suggest to do this approach but my boss declined

 

[Nanashi7562]

Assuming your data starts in row 3 of each sheet, try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, i As Long, dic As Object, val As String
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    v1 = srcWS.Range("A3").CurrentRegion.Value
    v2 = desWS.Range("A3").CurrentRegion.Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        val = v1(i, 1) & "|" & v1(i, 2) & "|" & v1(i, 3) & "|" & v1(i, 4)
        If Not dic.exists(val) Then
            dic.Add val, v1(i, 5) & "|" & v1(i, 6) & "|" & v1(i, 7) & "|" & v1(i, 8)
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        val = v2(i, 1) & "|" & v2(i, 2) & "|" & v2(i, 3) & "|" & v2(i, 4)
        If dic.exists(val) Then
            desWS.Range("E" & i + 2).Resize(, 4).Value = Array(Split(dic(val), "|")(0), Split(dic(val), "|")(1), Split(dic(val), "|")(2), Split(dic(val), "|")(3))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Change the sheet names (in red) to suit your needs.

Thankyou very much for this... let me test and get back to you

 

[alansidman]

An alternative is Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ID", "Name", "Cat", "Loc"}, {{"QT1", each List.Sum([Q1]), type number}, {"QT2", each List.Sum([Q2]), type number}, {"QT3", each List.Sum([Q3]), type number}, {"QT4", each List.Sum([Q4]), type number}})
in
    #"Grouped Rows"