Concatenate Column then sum it down

cahayagalax

New Member
Joined
Sep 26, 2023
Messages
1
Office Version
  1. 2003 or older
Platform
  1. Windows
Hi All,

how do I create a concatenate macro in column A from column B (code) then based on the code that has been combined, column D to column H, add them up to the bottom.

I attached an example file



SO.xlsm
ABCDEFGH
1STOK OPNAM
2NEW CODECODEBARANGKUARTAL 1KUARTAL 2KUARTAL 3KUARTAL 4JUMLAH
3abcdefg = c + d + e + f
4
5AMABES A
6A1GUDANG A1
7A1.ATOKO 1
81.1.1.1SHAMPO A0410
91.1.1.2SHAMPO B1314
101.1.1.3SHAMPO C2040
111.1.1.4SHAMPO D6100
122.1.1.1SABUN A0704
132.1.1.2SABUN B6070
143.1.1.1ODOL A0005
153.1.1.2ODOL B4000
163.1.1.3ODOL C0300
17A2GUDANG 2
18A2.ATOKO 1
191.1.1.2SHAMPO B5050
201.1.1.3SHAMPO C0030
212.1.1.2SABUN B0440
223.1.1.1ODOL A1012
23A2.CTOKO 3
241.1.1.4SHAMPO D3030
252.1.1.1SABUN A0130
263.1.1.2ODOL B0004
273.1.1.3ODOL C1020
28
29BMABES B
30B2GUDANG 2
31B2.ATOKO 1
321.1.1.1SHAMPO A3030
331.1.1.2SHAMPO B0200
341.1.1.3SHAMPO C0000
351.1.1.4SHAMPO D0010
362.1.1.1SABUN A3100
372.1.1.2SABUN B0030
383.1.1.1ODOL A0200
393.1.1.2ODOL B3000
403.1.1.3ODOL C0330
413.1.1.4ODOL D0201
423.1.1.5ODOL E0020
43
44CMABES C
45C5GUDANG 5
46C5.ATOKO 1
471.1.1.1SHAMPO A1000
481.1.1.2SHAMPO B0200
491.1.1.3SHAMPO C0120
501.1.1.4SHAMPO D0200
512.1.1.1SABUN A0031
522.1.1.2SABUN B0007
533.1.1.1ODOL A0405
543.1.1.2ODOL B3000
553.1.1.3ODOL C0600
563.1.1.4ODOL D0063
573.1.1.5ODOL E1321
58C5.BTOKO 2
591.1.1.4SHAMPO D2345
602.1.1.1SABUN A0332
613.1.1.1ODOL A1230
623.1.1.2ODOL B0033
633.1.1.5ODOL E2123
64C5.CTOKO 3
651.1.1.4SHAMPO D3030
662.1.1.1SABUN A0130
673.1.1.2ODOL B0004
683.1.1.3ODOL C1020
69
70TOTAL MABES
71
BEFORE
Cell Formulas
RangeFormula
F19,F67,F65,F61,F32,F26,F24,F21:F22F19=SUM(C19:E19)





this is the result i want


SO.xlsm
ABCDEFGH
1STOK OPNAM
2NEW CODECODEBARANGKUARTAL 1KUARTAL 2KUARTAL 3KUARTAL 4JUMLAH
3abcdefg = c + d + e + f
4
5AMABES A29233419105
6A1GUDANG A11918131363
7A1.AA1.ATOKO 11918131363
8A1.A-1.1.1.11.1.1.1SHAMPO A04105
9A1.A-1.1.1.21.1.1.2SHAMPO B13149
10A1.A-1.1.1.31.1.1.3SHAMPO C20406
11A1.A-1.1.1.41.1.1.4SHAMPO D61007
12A1.A-2.1.1.12.1.1.1SABUN A070411
13A1.A-2.1.1.22.1.1.2SABUN B607013
14A1.A-3.1.1.13.1.1.1ODOL A00055
15A1.A-3.1.1.23.1.1.2ODOL B40004
16A1.A-3.1.1.33.1.1.3ODOL C03003
17A2GUDANG 210521642
18A2.AA2.ATOKO 16413225
19A2.A-1.1.1.21.1.1.2SHAMPO B505010
20A2.A-1.1.1.31.1.1.3SHAMPO C00303
21A2.A-2.1.1.22.1.1.2SABUN B04408
22A2.A-3.1.1.13.1.1.1ODOL A10124
23A2.CA2.CTOKO 3418417
24A2.C-1.1.1.41.1.1.4SHAMPO D30306
25A2.C-2.1.1.12.1.1.1SABUN A01304
26A2.C-3.1.1.23.1.1.2ODOL B00044
27A2.C-3.1.1.33.1.1.3ODOL C10203
28
29BMABES B91012132
30B2GUDANG 291012132
31B2.AB2.ATOKO 191012132
32B2.A-1.1.1.11.1.1.1SHAMPO A30306
33B2.A-1.1.1.21.1.1.2SHAMPO B02002
34B2.A-1.1.1.31.1.1.3SHAMPO C00000
35B2.A-1.1.1.41.1.1.4SHAMPO D00101
36B2.A-2.1.1.12.1.1.1SABUN A31004
37B2.A-2.1.1.22.1.1.2SABUN B00303
38B2.A-3.1.1.13.1.1.1ODOL A02002
39B2.A-3.1.1.23.1.1.2ODOL B30003
40B2.A-3.1.1.33.1.1.3ODOL C03306
41B2.A-3.1.1.43.1.1.4ODOL D02013
42B2.A-3.1.1.53.1.1.5ODOL E00202
43
44CMABES C14283634112
45C5GUDANG 514283634112
46C5.AC5.ATOKO 1518131753
47C5.A-1.1.1.11.1.1.1SHAMPO A10001
48C5.A-1.1.1.21.1.1.2SHAMPO B02002
49C5.A-1.1.1.31.1.1.3SHAMPO C01203
50C5.A-1.1.1.41.1.1.4SHAMPO D02002
51C5.A-2.1.1.12.1.1.1SABUN A00314
52C5.A-2.1.1.22.1.1.2SABUN B00077
53C5.A-3.1.1.13.1.1.1ODOL A04059
54C5.A-3.1.1.23.1.1.2ODOL B30003
55C5.A-3.1.1.33.1.1.3ODOL C06006
56C5.A-3.1.1.43.1.1.4ODOL D00639
57C5.A-3.1.1.53.1.1.5ODOL E13217
58C5.BC5.BTOKO 259151342
59C5.B-1.1.1.41.1.1.4SHAMPO D234514
60C5.B-2.1.1.12.1.1.1SABUN A03328
61C5.B-3.1.1.13.1.1.1ODOL A12306
62C5.B-3.1.1.23.1.1.2ODOL B00336
63C5.B-3.1.1.53.1.1.5ODOL E21238
64C5.CC5.CTOKO 3418417
65C5.C-1.1.1.41.1.1.4SHAMPO D30306
66C5.C-2.1.1.12.1.1.1SABUN A01304
67C5.C-3.1.1.23.1.1.2ODOL B00044
68C5.C-3.1.1.33.1.1.3ODOL C10203
69
70TOTAL MABES52618254249
71
72
I WANT LIKE THIS
Cell Formulas
RangeFormula
D5:H5D5=D6+D17
D44:H44,D29:H30,D6:H6D6=D7
D7:H7D7=SUM(D8:D16)
A7,A64,A58,A46,A31,A23,A18A7=B7
A8:A16A8=$A$7&"-"&B8
D17:H17D17=D18+D23
D64:H64,D23:H23,D18:H18D18=SUM(D19:D22)
F19,F67,F65,F61,F32,F26,F24,F21:F22F19=SUM(C19:E19)
A19:A22A19=$A$18&"-"&B19
A24:A27A24=$A$23&"-"&B24
H65:H68,H59:H63,H47:H57,H32:H42,H24:H27,H19:H22,H8:H16H8=SUM(D8:G8)
D46:H46,D31:H31D31=SUM(D32:D42)
A32:A42A32=$A$31&"-"&B32
D45:H45D45=D46+D58+D64
D58:H58D58=SUM(D59:D63)
A47:A57A47=$A$46&"-"&B47
A59:A63A59=$A$58&"-"&B59
A65:A68A65=$A$64&"-"&B65
D70:H70D70=D5+D29+D44
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top