cahayagalax
New Member
- Joined
- Sep 26, 2023
- Messages
- 1
- Office Version
- 2003 or older
- Platform
- 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
this is the result i want
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 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | STOK OPNAM | |||||||||
2 | NEW CODE | CODE | BARANG | KUARTAL 1 | KUARTAL 2 | KUARTAL 3 | KUARTAL 4 | JUMLAH | ||
3 | a | b | c | d | e | f | g = c + d + e + f | |||
4 | ||||||||||
5 | A | MABES A | ||||||||
6 | A1 | GUDANG A1 | ||||||||
7 | A1.A | TOKO 1 | ||||||||
8 | 1.1.1.1 | SHAMPO A | 0 | 4 | 1 | 0 | ||||
9 | 1.1.1.2 | SHAMPO B | 1 | 3 | 1 | 4 | ||||
10 | 1.1.1.3 | SHAMPO C | 2 | 0 | 4 | 0 | ||||
11 | 1.1.1.4 | SHAMPO D | 6 | 1 | 0 | 0 | ||||
12 | 2.1.1.1 | SABUN A | 0 | 7 | 0 | 4 | ||||
13 | 2.1.1.2 | SABUN B | 6 | 0 | 7 | 0 | ||||
14 | 3.1.1.1 | ODOL A | 0 | 0 | 0 | 5 | ||||
15 | 3.1.1.2 | ODOL B | 4 | 0 | 0 | 0 | ||||
16 | 3.1.1.3 | ODOL C | 0 | 3 | 0 | 0 | ||||
17 | A2 | GUDANG 2 | ||||||||
18 | A2.A | TOKO 1 | ||||||||
19 | 1.1.1.2 | SHAMPO B | 5 | 0 | 5 | 0 | ||||
20 | 1.1.1.3 | SHAMPO C | 0 | 0 | 3 | 0 | ||||
21 | 2.1.1.2 | SABUN B | 0 | 4 | 4 | 0 | ||||
22 | 3.1.1.1 | ODOL A | 1 | 0 | 1 | 2 | ||||
23 | A2.C | TOKO 3 | ||||||||
24 | 1.1.1.4 | SHAMPO D | 3 | 0 | 3 | 0 | ||||
25 | 2.1.1.1 | SABUN A | 0 | 1 | 3 | 0 | ||||
26 | 3.1.1.2 | ODOL B | 0 | 0 | 0 | 4 | ||||
27 | 3.1.1.3 | ODOL C | 1 | 0 | 2 | 0 | ||||
28 | ||||||||||
29 | B | MABES B | ||||||||
30 | B2 | GUDANG 2 | ||||||||
31 | B2.A | TOKO 1 | ||||||||
32 | 1.1.1.1 | SHAMPO A | 3 | 0 | 3 | 0 | ||||
33 | 1.1.1.2 | SHAMPO B | 0 | 2 | 0 | 0 | ||||
34 | 1.1.1.3 | SHAMPO C | 0 | 0 | 0 | 0 | ||||
35 | 1.1.1.4 | SHAMPO D | 0 | 0 | 1 | 0 | ||||
36 | 2.1.1.1 | SABUN A | 3 | 1 | 0 | 0 | ||||
37 | 2.1.1.2 | SABUN B | 0 | 0 | 3 | 0 | ||||
38 | 3.1.1.1 | ODOL A | 0 | 2 | 0 | 0 | ||||
39 | 3.1.1.2 | ODOL B | 3 | 0 | 0 | 0 | ||||
40 | 3.1.1.3 | ODOL C | 0 | 3 | 3 | 0 | ||||
41 | 3.1.1.4 | ODOL D | 0 | 2 | 0 | 1 | ||||
42 | 3.1.1.5 | ODOL E | 0 | 0 | 2 | 0 | ||||
43 | ||||||||||
44 | C | MABES C | ||||||||
45 | C5 | GUDANG 5 | ||||||||
46 | C5.A | TOKO 1 | ||||||||
47 | 1.1.1.1 | SHAMPO A | 1 | 0 | 0 | 0 | ||||
48 | 1.1.1.2 | SHAMPO B | 0 | 2 | 0 | 0 | ||||
49 | 1.1.1.3 | SHAMPO C | 0 | 1 | 2 | 0 | ||||
50 | 1.1.1.4 | SHAMPO D | 0 | 2 | 0 | 0 | ||||
51 | 2.1.1.1 | SABUN A | 0 | 0 | 3 | 1 | ||||
52 | 2.1.1.2 | SABUN B | 0 | 0 | 0 | 7 | ||||
53 | 3.1.1.1 | ODOL A | 0 | 4 | 0 | 5 | ||||
54 | 3.1.1.2 | ODOL B | 3 | 0 | 0 | 0 | ||||
55 | 3.1.1.3 | ODOL C | 0 | 6 | 0 | 0 | ||||
56 | 3.1.1.4 | ODOL D | 0 | 0 | 6 | 3 | ||||
57 | 3.1.1.5 | ODOL E | 1 | 3 | 2 | 1 | ||||
58 | C5.B | TOKO 2 | ||||||||
59 | 1.1.1.4 | SHAMPO D | 2 | 3 | 4 | 5 | ||||
60 | 2.1.1.1 | SABUN A | 0 | 3 | 3 | 2 | ||||
61 | 3.1.1.1 | ODOL A | 1 | 2 | 3 | 0 | ||||
62 | 3.1.1.2 | ODOL B | 0 | 0 | 3 | 3 | ||||
63 | 3.1.1.5 | ODOL E | 2 | 1 | 2 | 3 | ||||
64 | C5.C | TOKO 3 | ||||||||
65 | 1.1.1.4 | SHAMPO D | 3 | 0 | 3 | 0 | ||||
66 | 2.1.1.1 | SABUN A | 0 | 1 | 3 | 0 | ||||
67 | 3.1.1.2 | ODOL B | 0 | 0 | 0 | 4 | ||||
68 | 3.1.1.3 | ODOL C | 1 | 0 | 2 | 0 | ||||
69 | ||||||||||
70 | TOTAL MABES | |||||||||
71 | ||||||||||
BEFORE |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F19,F67,F65,F61,F32,F26,F24,F21:F22 | F19 | =SUM(C19:E19) |
this is the result i want
SO.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | STOK OPNAM | |||||||||
2 | NEW CODE | CODE | BARANG | KUARTAL 1 | KUARTAL 2 | KUARTAL 3 | KUARTAL 4 | JUMLAH | ||
3 | a | b | c | d | e | f | g = c + d + e + f | |||
4 | ||||||||||
5 | A | MABES A | 29 | 23 | 34 | 19 | 105 | |||
6 | A1 | GUDANG A1 | 19 | 18 | 13 | 13 | 63 | |||
7 | A1.A | A1.A | TOKO 1 | 19 | 18 | 13 | 13 | 63 | ||
8 | A1.A-1.1.1.1 | 1.1.1.1 | SHAMPO A | 0 | 4 | 1 | 0 | 5 | ||
9 | A1.A-1.1.1.2 | 1.1.1.2 | SHAMPO B | 1 | 3 | 1 | 4 | 9 | ||
10 | A1.A-1.1.1.3 | 1.1.1.3 | SHAMPO C | 2 | 0 | 4 | 0 | 6 | ||
11 | A1.A-1.1.1.4 | 1.1.1.4 | SHAMPO D | 6 | 1 | 0 | 0 | 7 | ||
12 | A1.A-2.1.1.1 | 2.1.1.1 | SABUN A | 0 | 7 | 0 | 4 | 11 | ||
13 | A1.A-2.1.1.2 | 2.1.1.2 | SABUN B | 6 | 0 | 7 | 0 | 13 | ||
14 | A1.A-3.1.1.1 | 3.1.1.1 | ODOL A | 0 | 0 | 0 | 5 | 5 | ||
15 | A1.A-3.1.1.2 | 3.1.1.2 | ODOL B | 4 | 0 | 0 | 0 | 4 | ||
16 | A1.A-3.1.1.3 | 3.1.1.3 | ODOL C | 0 | 3 | 0 | 0 | 3 | ||
17 | A2 | GUDANG 2 | 10 | 5 | 21 | 6 | 42 | |||
18 | A2.A | A2.A | TOKO 1 | 6 | 4 | 13 | 2 | 25 | ||
19 | A2.A-1.1.1.2 | 1.1.1.2 | SHAMPO B | 5 | 0 | 5 | 0 | 10 | ||
20 | A2.A-1.1.1.3 | 1.1.1.3 | SHAMPO C | 0 | 0 | 3 | 0 | 3 | ||
21 | A2.A-2.1.1.2 | 2.1.1.2 | SABUN B | 0 | 4 | 4 | 0 | 8 | ||
22 | A2.A-3.1.1.1 | 3.1.1.1 | ODOL A | 1 | 0 | 1 | 2 | 4 | ||
23 | A2.C | A2.C | TOKO 3 | 4 | 1 | 8 | 4 | 17 | ||
24 | A2.C-1.1.1.4 | 1.1.1.4 | SHAMPO D | 3 | 0 | 3 | 0 | 6 | ||
25 | A2.C-2.1.1.1 | 2.1.1.1 | SABUN A | 0 | 1 | 3 | 0 | 4 | ||
26 | A2.C-3.1.1.2 | 3.1.1.2 | ODOL B | 0 | 0 | 0 | 4 | 4 | ||
27 | A2.C-3.1.1.3 | 3.1.1.3 | ODOL C | 1 | 0 | 2 | 0 | 3 | ||
28 | ||||||||||
29 | B | MABES B | 9 | 10 | 12 | 1 | 32 | |||
30 | B2 | GUDANG 2 | 9 | 10 | 12 | 1 | 32 | |||
31 | B2.A | B2.A | TOKO 1 | 9 | 10 | 12 | 1 | 32 | ||
32 | B2.A-1.1.1.1 | 1.1.1.1 | SHAMPO A | 3 | 0 | 3 | 0 | 6 | ||
33 | B2.A-1.1.1.2 | 1.1.1.2 | SHAMPO B | 0 | 2 | 0 | 0 | 2 | ||
34 | B2.A-1.1.1.3 | 1.1.1.3 | SHAMPO C | 0 | 0 | 0 | 0 | 0 | ||
35 | B2.A-1.1.1.4 | 1.1.1.4 | SHAMPO D | 0 | 0 | 1 | 0 | 1 | ||
36 | B2.A-2.1.1.1 | 2.1.1.1 | SABUN A | 3 | 1 | 0 | 0 | 4 | ||
37 | B2.A-2.1.1.2 | 2.1.1.2 | SABUN B | 0 | 0 | 3 | 0 | 3 | ||
38 | B2.A-3.1.1.1 | 3.1.1.1 | ODOL A | 0 | 2 | 0 | 0 | 2 | ||
39 | B2.A-3.1.1.2 | 3.1.1.2 | ODOL B | 3 | 0 | 0 | 0 | 3 | ||
40 | B2.A-3.1.1.3 | 3.1.1.3 | ODOL C | 0 | 3 | 3 | 0 | 6 | ||
41 | B2.A-3.1.1.4 | 3.1.1.4 | ODOL D | 0 | 2 | 0 | 1 | 3 | ||
42 | B2.A-3.1.1.5 | 3.1.1.5 | ODOL E | 0 | 0 | 2 | 0 | 2 | ||
43 | ||||||||||
44 | C | MABES C | 14 | 28 | 36 | 34 | 112 | |||
45 | C5 | GUDANG 5 | 14 | 28 | 36 | 34 | 112 | |||
46 | C5.A | C5.A | TOKO 1 | 5 | 18 | 13 | 17 | 53 | ||
47 | C5.A-1.1.1.1 | 1.1.1.1 | SHAMPO A | 1 | 0 | 0 | 0 | 1 | ||
48 | C5.A-1.1.1.2 | 1.1.1.2 | SHAMPO B | 0 | 2 | 0 | 0 | 2 | ||
49 | C5.A-1.1.1.3 | 1.1.1.3 | SHAMPO C | 0 | 1 | 2 | 0 | 3 | ||
50 | C5.A-1.1.1.4 | 1.1.1.4 | SHAMPO D | 0 | 2 | 0 | 0 | 2 | ||
51 | C5.A-2.1.1.1 | 2.1.1.1 | SABUN A | 0 | 0 | 3 | 1 | 4 | ||
52 | C5.A-2.1.1.2 | 2.1.1.2 | SABUN B | 0 | 0 | 0 | 7 | 7 | ||
53 | C5.A-3.1.1.1 | 3.1.1.1 | ODOL A | 0 | 4 | 0 | 5 | 9 | ||
54 | C5.A-3.1.1.2 | 3.1.1.2 | ODOL B | 3 | 0 | 0 | 0 | 3 | ||
55 | C5.A-3.1.1.3 | 3.1.1.3 | ODOL C | 0 | 6 | 0 | 0 | 6 | ||
56 | C5.A-3.1.1.4 | 3.1.1.4 | ODOL D | 0 | 0 | 6 | 3 | 9 | ||
57 | C5.A-3.1.1.5 | 3.1.1.5 | ODOL E | 1 | 3 | 2 | 1 | 7 | ||
58 | C5.B | C5.B | TOKO 2 | 5 | 9 | 15 | 13 | 42 | ||
59 | C5.B-1.1.1.4 | 1.1.1.4 | SHAMPO D | 2 | 3 | 4 | 5 | 14 | ||
60 | C5.B-2.1.1.1 | 2.1.1.1 | SABUN A | 0 | 3 | 3 | 2 | 8 | ||
61 | C5.B-3.1.1.1 | 3.1.1.1 | ODOL A | 1 | 2 | 3 | 0 | 6 | ||
62 | C5.B-3.1.1.2 | 3.1.1.2 | ODOL B | 0 | 0 | 3 | 3 | 6 | ||
63 | C5.B-3.1.1.5 | 3.1.1.5 | ODOL E | 2 | 1 | 2 | 3 | 8 | ||
64 | C5.C | C5.C | TOKO 3 | 4 | 1 | 8 | 4 | 17 | ||
65 | C5.C-1.1.1.4 | 1.1.1.4 | SHAMPO D | 3 | 0 | 3 | 0 | 6 | ||
66 | C5.C-2.1.1.1 | 2.1.1.1 | SABUN A | 0 | 1 | 3 | 0 | 4 | ||
67 | C5.C-3.1.1.2 | 3.1.1.2 | ODOL B | 0 | 0 | 0 | 4 | 4 | ||
68 | C5.C-3.1.1.3 | 3.1.1.3 | ODOL C | 1 | 0 | 2 | 0 | 3 | ||
69 | ||||||||||
70 | TOTAL MABES | 52 | 61 | 82 | 54 | 249 | ||||
71 | ||||||||||
72 | ||||||||||
I WANT LIKE THIS |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D5:H5 | D5 | =D6+D17 |
D44:H44,D29:H30,D6:H6 | D6 | =D7 |
D7:H7 | D7 | =SUM(D8:D16) |
A7,A64,A58,A46,A31,A23,A18 | A7 | =B7 |
A8:A16 | A8 | =$A$7&"-"&B8 |
D17:H17 | D17 | =D18+D23 |
D64:H64,D23:H23,D18:H18 | D18 | =SUM(D19:D22) |
F19,F67,F65,F61,F32,F26,F24,F21:F22 | F19 | =SUM(C19:E19) |
A19:A22 | A19 | =$A$18&"-"&B19 |
A24:A27 | A24 | =$A$23&"-"&B24 |
H65:H68,H59:H63,H47:H57,H32:H42,H24:H27,H19:H22,H8:H16 | H8 | =SUM(D8:G8) |
D46:H46,D31:H31 | D31 | =SUM(D32:D42) |
A32:A42 | A32 | =$A$31&"-"&B32 |
D45:H45 | D45 | =D46+D58+D64 |
D58:H58 | D58 | =SUM(D59:D63) |
A47:A57 | A47 | =$A$46&"-"&B47 |
A59:A63 | A59 | =$A$58&"-"&B59 |
A65:A68 | A65 | =$A$64&"-"&B65 |
D70:H70 | D70 | =D5+D29+D44 |