Merge 1000 items and 10000 rows for duplicates items from multiple columns

[abdo meghari]

Hi experts
I need merging column B,C,D into one column and sum value in column E for duplicates items .
the original data

SP.xlsm
	A	B	C	D	E
3	ITEM	BRAND	TYPE	ORIGIN	BALANCE
4	1	245/40R18	RE050A	JAP	36
5	2	245/40R20	RE050A	JAP	19
6	3	245/45R17	RE050A	JAP	15
7	4	245/45R17	EA01	JAP	1
8	5	245/45R19	S-03	JAP	7
9	6	245/40R20	RE050A	JAP	20
10	7	245/45R17	RE050A	JAP	22
11	8	245/65R17	D697	INDO	25
12	9	245/70R16	D694	JAP	3
13	10	245/70R16	D697	INDO	1
14	11	245/75R16	D697	INDO	12
15	12	245/75R17	693A	JAP	623
16	13	255/30R19	RE050A	JAP	12
17	14	255/35R18	GR90	JAP	14
18	15	255/35R20	RE050A	JAP	8
BMS

the result

SP.xlsm
	A	B	C
3	ITEM	BRAND	BALANCE
4	1	245/40R18 RE050A JAP	36
5	2	245/40R20 RE050A JAP	39
6	3	245/45R17 RE050A JAP	37
7	4	245/45R17 EA01 JAP	1
8	5	245/45R19 S-03 JAP	7
9	6	245/65R17 D697 INDO	25
10	7	245/70R16 D694 JAP	3
11	8	245/70R16 D697 INDO	1
12	9	245/75R16 D697 INDO	12
13	10	245/75R17 693A JAP	623
14	11	255/30R19 RE050A JAP	12
15	12	255/35R18 GR90 JAP	14
16	13	255/35R20 RE050A JAP	8
RESULT

and should update in result sheet based on updating BMS sheet when run the macro every time .
I have about 1000 items and could repeat for 10000 rows
thanks

 

[DanteAmor]

You can try a pivot table.

First concatenate the data:

Dante Amor
	A	B	C	D	E	F
1	ITEM	BRAND	TYPE	ORIGIN	BALANCE	BRAND TYPE ORIGIN
2	1	245/40R18	RE050A	JAP	36	245/40R18 RE050A JAP
3	2	245/40R20	RE050A	JAP	19	245/40R20 RE050A JAP
4	3	245/45R17	RE050A	JAP	15	245/45R17 RE050A JAP
5	4	245/45R17	EA01	JAP	1	245/45R17 EA01 JAP
6	5	245/45R19	S-03	JAP	7	245/45R19 S-03 JAP
7	6	245/40R20	RE050A	JAP	20	245/40R20 RE050A JAP
8	7	245/45R17	RE050A	JAP	22	245/45R17 RE050A JAP
9	8	245/65R17	D697	INDO	25	245/65R17 D697 INDO
10	9	245/70R16	D694	JAP	3	245/70R16 D694 JAP
11	10	245/70R16	D697	INDO	1	245/70R16 D697 INDO
12	11	245/75R16	D697	INDO	12	245/75R16 D697 INDO
13	12	245/75R17	693A	JAP	623	245/75R17 693A JAP
14	13	255/30R19	RE050A	JAP	12	255/30R19 RE050A JAP
15	14	255/35R18	GR90	JAP	14	255/35R18 GR90 JAP
16	15	255/35R20	RE050A	JAP	8	255/35R20 RE050A JAP
Hoja1

Cell Formulas
Range	Formula
F2:F16	F2	=B2&" "&C2&" "&D2

And then create a pivot table:

When you add more information just update the pivot table.

 

[abdo meghari]

Thanks Dante , I don't used PT .
if it's possible by Vba.

 

[kevin9999]

Hi Abdo, please try the following on a copy of your data. (Assumes your data starts on row 4 as you've indicated).

Sub Abdo_V1()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("BMS")
    Set ws2 = Worksheets("RESULT")
    
    Dim rng As Range, R As Range, txt As String
    Dim i As Long, j As Long, n As Long, ar
    Set rng = ws1.Range("B4:B" & ws1.Cells(Rows.Count, "B").End(xlUp).Row)
    ar = ws1.Range("B4", ws1.Cells(Rows.Count, "E").End(xlUp))

    With CreateObject("scripting.dictionary")
        For Each R In rng
            txt = R.Value & " " & R.Offset(0, 1).Value & " " & R.Offset(0, 2).Value
        If Not .exists(txt) Then
            n = n + 1
            .Add txt, n
            For j = 1 To UBound(ar, 2)
                ar(n, j) = R.Offset(, j - 1)
            Next j
        Else
            ar(.Item(txt), 4) = ar(.Item(txt), 4) + R.Offset(, 3)
        End If
        Next R
    End With
    
    For i = 1 To UBound(ar)
        ar(i, 2) = ar(i, 1) & " " & ar(i, 2) & " " & ar(i, 3)
        ar(i, 1) = i
        ar(i, 3) = ar(i, 4)
    Next i
    ws2.Range("A3").Resize(, 3).Value = Array("ITEM", "BRAND", "BALANCE")
    ws2.Range("A4").Resize(n, 3) = ar
End Sub

 

[alansidman]

An alternative with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ITEM", Int64.Type}, {"BRAND", type text}, {"TYPE", type text}, {"ORIGIN", type text}, {"BALANCE", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"BRAND", "TYPE", "ORIGIN"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"New Balance", each List.Sum([BALANCE]), type nullable number}})
in
    #"Grouped Rows"

Book2
	A	B	C	D	E	F	G	H
1	ITEM	BRAND	TYPE	ORIGIN	BALANCE		Merged	New Balance
2	1	245/40R18	RE050A	JAP	36		245/40R18 RE050A JAP	36
3	2	245/40R20	RE050A	JAP	19		245/40R20 RE050A JAP	39
4	3	245/45R17	RE050A	JAP	15		245/45R17 RE050A JAP	37
5	4	245/45R17	EA01	JAP	1		245/45R17 EA01 JAP	1
6	5	245/45R19	S-03	JAP	7		245/45R19 S-03 JAP	7
7	6	245/40R20	RE050A	JAP	20		245/65R17 D697 INDO	25
8	7	245/45R17	RE050A	JAP	22		245/70R16 D694 JAP	3
9	8	245/65R17	D697	INDO	25		245/70R16 D697 INDO	1
10	9	245/70R16	D694	JAP	3		245/75R16 D697 INDO	12
11	10	245/70R16	D697	INDO	1		245/75R17 693A JAP	623
12	11	245/75R16	D697	INDO	12		255/30R19 RE050A JAP	12
13	12	245/75R17	693A	JAP	623		255/35R18 GR90 JAP	14
14	13	255/30R19	RE050A	JAP	12		255/35R20 RE050A JAP	8
15	14	255/35R18	GR90	JAP	14
16	15	255/35R20	RE050A	JAP	8
Sheet1

 

[abdo meghari]

@kevin9999
Really fast & great
can sort from small to big after merging please?

 

[DanteAmor]

I don't used PT .
if it's possible by Vba.

Then try this macro:

Sub MergeItems()
  Dim dic As Object, sh As Worksheet, a, i&, ky$
  Set sh = Sheets("BMS")
  Set dic = CreateObject("Scripting.Dictionary")
  
  a = sh.Range("A4", sh.Range("E" & Rows.Count).End(3)).Value
  For i = 1 To UBound(a, 1)
    ky = Trim(a(i, 2)) & " " & Trim(a(i, 3)) & " " & Trim(a(i, 4))
    dic(ky) = dic(ky) + a(i, 5)
  Next
  With Sheets("RESULT")
    .Cells.ClearContents
    .Range("B4").Resize(dic.Count, 2) = Application.Transpose(Array(dic.keys, dic.items))
    .Range("A4").Value = 1
    .Range("A4").AutoFill .Range("A4:A" & dic.Count + 3), xlFillSeries
    sh.Range("A3, B3, E3").Copy .Range("A3")
  End With
End Sub

 

[DanteAmor]

can sort from small to big after merging

Hi abdo:

The try this:

Sub MergeItems()
  Dim dic As Object, sh As Worksheet, a, i&, ky$
  Set sh = Sheets("BMS")
  Set dic = CreateObject("Scripting.Dictionary")
 
  a = sh.Range("A4", sh.Range("E" & Rows.Count).End(3)).Value
  For i = 1 To UBound(a, 1)
    ky = Trim(a(i, 2)) & " " & Trim(a(i, 3)) & " " & Trim(a(i, 4))
    dic(ky) = dic(ky) + a(i, 5)
  Next
  With Sheets("RESULT")
    .Cells.ClearContents
    .Range("B4").Resize(dic.Count, 2) = Application.Transpose(Array(dic.keys, dic.items))
    .Range("B4:C" & dic.Count + 3).Sort key1:=.Range("C3"), order1:=xlAscending, Header:=xlNo
    .Range("A4").Value = 1
    .Range("A4").AutoFill .Range("A4:A" & dic.Count + 3), xlFillSeries
    sh.Range("A3, B3, E3").Copy .Range("A3")
  End With
End Sub

 

[kevin9999]

@kevin9999
Really fast & great
can sort from small to big after merging please?

Thanks for the feedback Aldo, although I recommend you use the code provided by @DanteAmor in post #8 as it is more concise & efficient to what I provided. Suggest you change the accepted solution accordingly.

 

[abdo meghari]

Hi Dante
first thank you for the code
second I have question about sorting ,does sort this data are correct ?

SP.xlsm
	A	B	C
3	ITEM	BRAND	BALANCE
4	1	245/45R17 EA01 JAP	1
5	2	245/70R16 D697 INDO	1
6	3	255/50R19 ER30 JAP	1
7	4	255/70R15C D697 THI	1
8	5	26.5R25 VSDTZ JAP	1
9	6	265/65R17 D697 INDO	1
10	7	285/45R19 ER30 JAP	1
11	8	295/80R22.5 M840 JAP	1
12	9	300-15 JL INDO	1
13	10	315/80R22.5 R294 JAP	1
14	11	600-14 LT MRA JAP	1
15	12	825-15 14PR JLA INDO	1
16	13	9.5R17.5 M716 JAP	1
17	14	LT 235/85R16 D697 INDO	1
18	15	LT255/70R16 D697 THI	1
19	16	1200R20 M857 JAP	1
20	17	1200R20 G550 JAP	1
21	18	275/35R20 S001 JAP	2
22	19	275/70R16 D694 JAP	2
23	20	29.5R29 **VKT JAP	2
24	21	750R16 FSM JAP	2
25	22	P225/75R16 D697 INDO	2
26	23	245/70R16 D694 JAP	3
27	24	29.5R25 **VKT JAP	3
28	25	315/35R20 DHPZ JAP	3
29	26	7.5-15/6.00 SOLID PJLS JAP	3
30	27	700-12 JAP	3
31	28	LT215/75R15 D694 JAP	3
32	29	275/40R20 DHPS JAP	4
33	30	29.5R29 VSNTZ JAP	4
34	31	750R16 LT D694 JAP	4
35	32	245/45R19 S-03 JAP	7
36	33	255/45ZR17 RE050A JAP	7
37	34	700R20 R187 JAP	7
38	35	255/35R20 RE050A JAP	8
39	36	P265/50R20 DHPA JAP	8
40	37	LT225/75R16 T697 INDO	9
41	38	255/40R17 RE050A JAP	10
42	39	275/35R18 EA01 JAP	10
43	40	28×9-15 PL01 JAP	10
44	41	6.50-10 10PR BKT PL01 INDIA	10
45	42	10-17.5 R180 JAP	11
46	43	245/75R16 D697 INDO	12
47	44	255/30R19 RE050A JAP	12
48	45	275/40R18 RE050A JAP	12
49	46	29.5R25 **VSDL JAP	12
50	47	1100R20 MICHILEN THI	12
51	48	255/35R18 GR90 JAP	14
52	49	255/40R18 RE001 JAP	15
53	50	600-9 6PR JLA INDO	16
54	51	P235/55R17 D400 JAP	16
55	52	275/35R18 RE050A JAP	19
56	53	285/30R20 RE050A JAP	20
57	54	245/40R22 RE050A JAP	20
58	55	275/55R20 D680 JAP	22
59	56	255/45ZR17 S-01 JAP	23
60	57	245/65R17 D697 INDO	25
61	58	245/40R24 RE050A JAP	25
62	59	275/30R20 RE050A JAP	26
63	60	245/45R17 RE050A JAP	37
64	61	245/40R20 RE050A JAP	39
65	62	700-12 PL01 JAP	45
66	63	285/60R18 H/L850 JAP	62
67	64	1200R22.5 R187 JAP	79
68	65	245/75R17 693A JAP	623
69	66	750R16 VSJ JAP	1177
RESULT