abdo meghari
Well-known Member
- Joined
- Aug 3, 2021
- Messages
- 704
- Office Version
- 2019
Hello,
I would macro to deal with about 10000 rows and contain about 100 groups for each sheet.
in SELLING,BUYING sheet should split data based on column B for each group ,as to column C should match with sheets names(SALES,COSTING) are already existed and show data for each sheet based on sheet name is existed in column C for SELLING,BUYING sheets.
when split data if the C3,E3 are empty in SPLIT sheet
then should split data like this
otherwise when there are dates then split within dates in C3,E3 in SPLIT sheet.
thanks.
I would macro to deal with about 10000 rows and contain about 100 groups for each sheet.
in SELLING,BUYING sheet should split data based on column B for each group ,as to column C should match with sheets names(SALES,COSTING) are already existed and show data for each sheet based on sheet name is existed in column C for SELLING,BUYING sheets.
ABDO (1).xls | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | DATE | GROUP/COMPANY | TYPE | BRAND NO | DESCRIBE | UNIT | QTY | SELLING PRICE | TOTAL | ||
2 | 01/01/2025 | GOMMEST | SALES | 1769 | BLUE TR414 100 PCS | PIECE | 25 | 0.450 | 11.250 | ||
3 | 01/01/2025 | TIRES | SALES | 1130 | GC 1200R20 QAZ183 CHI | PIECE | 5 | 1,450.000 | 7,250.000 | ||
4 | 01/01/2025 | BATTERY | SALES | 1118 | XPRO 70A L KOR | PIECE | 1 | 350.000 | 350.000 | ||
5 | 01/01/2025 | BATTERY | SALES | 1119 | XPRO 70A R KOR | PIECE | 2 | 300.000 | 600.000 | ||
6 | 02/01/2025 | GOMMEST | SALES | 1774 | BOAF0-50 | PIECE | 210 | 0.900 | 189.000 | ||
7 | 03/01/2025 | GOMMEST | SALES | 1775 | XIA TW-9250 250ML | PIECE | 1 | 0.950 | 0.950 | ||
8 | 04/01/2025 | TIRES | SALES | 1123 | BS 750R16 R230 JAP | PIECE | 10 | 450.000 | 4,500.000 | ||
9 | 05/01/2025 | TIRES | SALES | 1124 | BS 750R16 VSJ JAP | PIECE | 8 | 750.000 | 6,000.000 | ||
10 | 06/01/2025 | GOMMEST | SALES | 1752 | EUS-65 ROUND PATCH 65X65 | PIECE | 10 | 2.500 | 25.000 | ||
11 | 06/01/2025 | GOMMEST | SALES | 1758 | P1108 TAIWAN | PIECE | 11 | 5.500 | 60.500 | ||
12 | 06/01/2025 | GOMMEST | SALES | 1773 | PTO-2301-R 0.6X100MM. | PIECE | 88 | 1.500 | 132.000 | ||
13 | 06/01/2025 | GOMMEST | SALES | 1765 | VMTC 7.50-16 V3-02-7 | PIECE | 1 | 60.000 | 60.000 | ||
14 | 06/01/2025 | TIRES | SALES | 1125 | BS 1200R20 G580 JAP | PIECE | 2 | 2,500.000 | 5,000.000 | ||
15 | 06/01/2025 | TIRES | SALES | 1126 | BS 315/80R22.5 R184 JAP | PIECE | 80 | 1,950.000 | 156,000.000 | ||
16 | 07/01/2025 | TIRES | SALES | 1127 | BS 1400R20 VSJ JAP | PIECE | 1 | 4,000.000 | 4,000.000 | ||
17 | 08/01/2025 | GOMMEST | SALES | 1767 | HEADER OF PRESSURE | PIECE | 1 | 4.000 | 4.000 | ||
18 | 09/01/2025 | GOMMEST | SALES | 1768 | LEAD 10GM WIDE | PIECE | 3 | 0.750 | 2.250 | ||
19 | 10/01/2025 | GOMMEST | SALES | 1769 | LEAD 10GM TIDE | PIECE | 7 | 0.850 | 5.950 | ||
20 | 11/01/2025 | GOMMEST | SALES | 1770 | LEAD 15GM WIDE | PIECE | 4 | 0.650 | 2.600 | ||
21 | 12/01/2025 | GOMMEST | SALES | 1771 | LEAD 15GM TIDE | PIECE | 6 | 0.950 | 5.700 | ||
22 | 13/01/2025 | GOMMEST | SALES | 1772 | LEAD 20GM WIDE | PIECE | 7 | 0.700 | 4.900 | ||
23 | 14/01/2025 | GOMMEST | SALES | 1773 | LEAD 20GM TIDE | PIECE | 16 | 0.800 | 12.800 | ||
24 | 15/01/2025 | TIRES | SALES | 1128 | BS 1200R24 G580 JAP | PIECE | 2 | 2,800.000 | 5,600.000 | ||
25 | 16/01/2025 | TIRES | SALES | 1129 | GC 1200R20 AZ026 CHI | PIECE | 2 | 1,350.000 | 2,700.000 | ||
26 | 17/01/2025 | BATTERY | SALES | 1120 | XPRO 90A R KOR | PIECE | 2 | 425.000 | 850.000 | ||
27 | 18/01/2025 | BATTERY | SALES | 1121 | HANKOOK 150A L KOR | PIECE | 10 | 850.000 | 8,500.000 | ||
28 | 19/01/2025 | BATTERY | SALES | 1123 | ASIMCO 150A L KOR | PIECE | 1 | 750.000 | 750.000 | ||
SELLING |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I2:I28 | I2 | =H2*G2 |
ABDO (1).xls | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | DATE | GROUP/COMPANY | TYPE | BRAND NO | DESCRIBE | UNIT | QTY | COSTING PRICE | TOTAL | ||
2 | 01/01/2025 | GOMMEST | COSTING | 1765 | VMTC 7.50-16 V3-02-7 | PIECE | 100 | 45.000 | 4,500.000 | ||
3 | 02/01/2025 | GOMMEST | COSTING | 1767 | HEADER OF PRESSURE | PIECE | 110 | 0.550 | 60.500 | ||
4 | 03/01/2025 | TIRES | COSTING | 1126 | BS 315/80R22.5 R184 JAP | PIECE | 100 | 1,900.000 | 190,000.000 | ||
5 | 04/01/2025 | TIRES | COSTING | 1127 | BS 1400R20 VSJ JAP | PIECE | 20 | 3,900.000 | 78,000.000 | ||
6 | 05/01/2025 | TIRES | COSTING | 1130 | GC 1200R20 QAZ183 CHI | PIECE | 30 | 1,400.000 | 42,000.000 | ||
7 | 05/01/2025 | TIRES | COSTING | 1133 | GC 1200R20 QAZ188 CHI | PIECE | 20 | 1,500.000 | 30,000.000 | ||
8 | 06/01/2025 | BATTERY | COSTING | 1118 | XPRO 70A L KOR | PIECE | 1 | 300.000 | 300.000 | ||
9 | 07/01/2025 | BATTERY | COSTING | 1119 | XPRO 70A R KOR | PIECE | 2 | 250.000 | 500.000 | ||
10 | 08/01/2025 | GOMMEST | COSTING | 1768 | LEAD 10GM WIDE | PIECE | 200 | 0.250 | 50.000 | ||
11 | 09/01/2025 | GOMMEST | COSTING | 1769 | BLUE TR414 100 PCS | PIECE | 125 | 0.330 | 41.250 | ||
12 | 10/01/2025 | GOMMEST | COSTING | 1752 | EUS-65 ROUND PATCH 65X65 | PIECE | 100 | 1.500 | 150.000 | ||
13 | 11/01/2025 | GOMMEST | COSTING | 1758 | P1108 TAIWAN | PIECE | 15 | 3.500 | 52.500 | ||
14 | 12/01/2025 | TIRES | COSTING | 1125 | BS 1200R20 G580 JAP | PIECE | 10 | 2,400.000 | 24,000.000 | ||
15 | 13/01/2025 | TIRES | COSTING | 1128 | BS 1200R24 G580 JAP | PIECE | 20 | 2,700.000 | 54,000.000 | ||
16 | 14/01/2025 | TIRES | COSTING | 1129 | GC 1200R20 AZ026 CHI | PIECE | 30 | 1,300.000 | 39,000.000 | ||
17 | 15/01/2025 | GOMMEST | COSTING | 1773 | PTO-2301-R 0.6X100MM. | PIECE | 90 | 1.250 | 112.500 | ||
18 | 16/01/2025 | GOMMEST | COSTING | 1775 | XIA TW-9250 250ML | PIECE | 12 | 0.490 | 5.880 | ||
19 | 17/01/2025 | GOMMEST | COSTING | 1769 | LEAD 10GM TIDE | PIECE | 17 | 0.350 | 5.950 | ||
20 | 18/01/2025 | GOMMEST | COSTING | 1770 | LEAD 15GM WIDE | PIECE | 44 | 0.440 | 19.360 | ||
21 | 19/01/2025 | GOMMEST | COSTING | 1771 | LEAD 15GM TIDE | PIECE | 66 | 0.450 | 29.700 | ||
22 | 20/01/2025 | GOMMEST | COSTING | 1772 | LEAD 20GM WIDE | PIECE | 77 | 0.460 | 35.420 | ||
23 | 21/01/2025 | GOMMEST | COSTING | 1773 | LEAD 20GM TIDE | PIECE | 166 | 0.470 | 78.020 | ||
24 | 21/01/2025 | GOMMEST | COSTING | 1774 | BOAF0-50 | PIECE | 250 | 0.480 | 120.000 | ||
25 | 21/01/2025 | GOMMEST | COSTING | 1775 | BOAF0-51 | PIECE | 100 | 0.650 | 65.000 | ||
26 | 21/01/2025 | TIRES | COSTING | 1123 | BS 750R16 R230 JAP | PIECE | 50 | 400.000 | 20,000.000 | ||
27 | 21/01/2025 | TIRES | COSTING | 1124 | BS 750R16 VSJ JAP | PIECE | 80 | 700.000 | 56,000.000 | ||
28 | 22/01/2025 | BATTERY | COSTING | 1122 | NOVA 150A L KOR | PIECE | 1 | 700.000 | 700.000 | ||
29 | 23/01/2025 | BATTERY | COSTING | 1123 | ASIMCO 150A L KOR | PIECE | 2 | 700.000 | 1,400.000 | ||
30 | 24/01/2025 | BATTERY | COSTING | 1120 | XPRO 90A R KOR | PIECE | 2 | 400.000 | 800.000 | ||
31 | 25/01/2025 | BATTERY | COSTING | 1121 | HANKOOK 150A L KOR | PIECE | 10 | 800.000 | 8,000.000 | ||
BUYING |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I2:I31 | I2 | =H2*G2 |
ABDO (1).xls | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | ||||||||||
2 | ||||||||||
3 | ||||||||||
4 | ||||||||||
5 | ||||||||||
6 | ||||||||||
7 | ||||||||||
8 | ||||||||||
9 | ||||||||||
10 | ||||||||||
11 | ||||||||||
12 | ||||||||||
13 | ||||||||||
14 | ||||||||||
15 | ||||||||||
16 | ||||||||||
17 | ||||||||||
18 | ||||||||||
19 | ||||||||||
20 | ||||||||||
21 | ||||||||||
SALES |
ABDO (1).xls | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | |||||||||
2 | |||||||||
3 | |||||||||
4 | |||||||||
5 | |||||||||
6 | |||||||||
7 | |||||||||
8 | |||||||||
9 | |||||||||
10 | |||||||||
11 | |||||||||
12 | |||||||||
13 | |||||||||
14 | |||||||||
15 | |||||||||
16 | |||||||||
17 | |||||||||
COSTING |
when split data if the C3,E3 are empty in SPLIT sheet
ABDO (1).xls | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | ||||||||
2 | FROM DATE | TO DATE | ||||||
3 | ||||||||
4 | ||||||||
5 | ||||||||
6 | ||||||||
7 | ||||||||
8 | ||||||||
split |
then should split data like this
ABDO (1).xls | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | GROUP/COMPANY: GOMMEST | ||||||||
2 | ITEM | BRAND NO | DESCRIBE | UNIT | QTY | SELLING PRICE | TOTAL | ||
3 | 1 | 1769 | BLUE TR414 100 PCS | PIECE | 25 | 0.450 | 11.250 | ||
4 | 2 | 1752 | EUS-65 ROUND PATCH 65X65 | PIECE | 10 | 2.500 | 25.000 | ||
5 | 3 | 1758 | P1108 TAIWAN | PIECE | 11 | 5.500 | 60.500 | ||
6 | 4 | 1773 | PTO-2301-R 0.6X100MM. | PIECE | 88 | 1.500 | 132.000 | ||
7 | 5 | 1765 | VMTC 7.50-16 V3-02-7 | PIECE | 1 | 60.000 | 60.000 | ||
8 | 6 | 1767 | HEADER OF PRESSURE | PIECE | 1 | 4.000 | 4.000 | ||
9 | 7 | 1768 | LEAD 10GM WIDE | PIECE | 3 | 0.750 | 2.250 | ||
10 | 8 | 1769 | LEAD 10GM TIDE | PIECE | 7 | 0.850 | 5.950 | ||
11 | 9 | 1770 | LEAD 15GM WIDE | PIECE | 4 | 0.650 | 2.600 | ||
12 | 10 | 1771 | LEAD 15GM TIDE | PIECE | 6 | 0.950 | 5.700 | ||
13 | 11 | 1772 | LEAD 20GM WIDE | PIECE | 7 | 0.700 | 4.900 | ||
14 | 12 | 1773 | LEAD 20GM TIDE | PIECE | 16 | 0.800 | 12.800 | ||
15 | 13 | 1774 | BOAF0-50 | PIECE | 210 | 0.900 | 189.000 | ||
16 | 14 | 1775 | XIA TW-9250 250ML | PIECE | 1 | 0.950 | 0.950 | ||
17 | SELLING TOTAL | 516.900 | |||||||
18 | |||||||||
19 | |||||||||
20 | |||||||||
21 | GROUP/COMPANY: TIRES | ||||||||
22 | ITEM | BRAND NO | DESCRIBE | UNIT | QTY | SELLING PRICE | TOTAL | ||
23 | 1 | 1123 | BS 750R16 R230 JAP | PIECE | 10 | 450.000 | 4,500.000 | ||
24 | 2 | 1124 | BS 750R16 VSJ JAP | PIECE | 8 | 750.000 | 6,000.000 | ||
25 | 3 | 1125 | BS 1200R20 G580 JAP | PIECE | 2 | 2,500.000 | 5,000.000 | ||
26 | 4 | 1126 | BS 315/80R22.5 R184 JAP | PIECE | 80 | 1,950.000 | 156,000.000 | ||
27 | 5 | 1127 | BS 1400R20 VSJ JAP | PIECE | 1 | 4,000.000 | 4,000.000 | ||
28 | 6 | 1128 | BS 1200R24 G580 JAP | PIECE | 2 | 2,800.000 | 5,600.000 | ||
29 | 7 | 1129 | GC 1200R20 AZ026 CHI | PIECE | 2 | 1,350.000 | 2,700.000 | ||
30 | 8 | 1130 | GC 1200R20 QAZ183 CHI | PIECE | 5 | 1,450.000 | 7,250.000 | ||
31 | SELLING TOTAL | 191,050.000 | |||||||
32 | |||||||||
33 | |||||||||
34 | GROUP/COMPANY: BATTERY | ||||||||
35 | ITEM | BRAND NO | DESCRIBE | UNIT | QTY | SELLING PRICE | TOTAL | ||
36 | 1 | 1118 | XPRO 70A L KOR | PIECE | 1 | 350.000 | 350.000 | ||
37 | 2 | 1119 | XPRO 70A R KOR | PIECE | 2 | 300.000 | 600.000 | ||
38 | 3 | 1120 | XPRO 90A R KOR | PIECE | 2 | 425.000 | 850.000 | ||
39 | 4 | 1121 | HANKOOK 150A L KOR | PIECE | 10 | 850.000 | 8,500.000 | ||
40 | 5 | 1123 | ASIMCO 150A L KOR | PIECE | 1 | 750.000 | 750.000 | ||
41 | SELLING TOTAL | 11,050.000 | |||||||
SALES |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G36:G40,G23:G30,G3:G16 | G3 | =F3*E3 |
G17 | G17 | =SUM(G3:G16) |
G31 | G31 | =SUM(G23:G30) |
G41 | G41 | =SUM(G36:G40) |
ABDO (1).xls | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | GROUP/COMPANY: GOMMEST | ||||||||
2 | ITEM | BRAND NO | DESCRIBE | UNIT | QTY | COSTING PRICE | TOTAL | ||
3 | 5 | 1765 | VMTC 7.50-16 V3-02-7 | PIECE | 100 | 45.000 | 4,500.000 | ||
4 | 6 | 1767 | HEADER OF PRESSURE | PIECE | 110 | 0.550 | 60.500 | ||
5 | 7 | 1768 | LEAD 10GM WIDE | PIECE | 200 | 0.250 | 50.000 | ||
6 | 1 | 1769 | BLUE TR414 100 PCS | PIECE | 125 | 0.330 | 41.250 | ||
7 | 2 | 1752 | EUS-65 ROUND PATCH 65X65 | PIECE | 100 | 1.500 | 150.000 | ||
8 | 3 | 1758 | P1108 TAIWAN | PIECE | 15 | 3.500 | 52.500 | ||
9 | 4 | 1773 | PTO-2301-R 0.6X100MM. | PIECE | 90 | 1.250 | 112.500 | ||
10 | 14 | 1775 | XIA TW-9250 250ML | PIECE | 12 | 0.490 | 5.880 | ||
11 | 8 | 1769 | LEAD 10GM TIDE | PIECE | 17 | 0.350 | 5.950 | ||
12 | 9 | 1770 | LEAD 15GM WIDE | PIECE | 44 | 0.440 | 19.360 | ||
13 | 10 | 1771 | LEAD 15GM TIDE | PIECE | 66 | 0.450 | 29.700 | ||
14 | 11 | 1772 | LEAD 20GM WIDE | PIECE | 77 | 0.460 | 35.420 | ||
15 | 12 | 1773 | LEAD 20GM TIDE | PIECE | 166 | 0.470 | 78.020 | ||
16 | 13 | 1774 | BOAF0-50 | PIECE | 250 | 0.480 | 120.000 | ||
17 | 14 | 1775 | BOAF0-51 | PIECE | 100 | 0.650 | 65.000 | ||
18 | COSTING TOTAL | 715.580 | |||||||
19 | |||||||||
20 | |||||||||
21 | GROUP/COMPANY: TIRES | ||||||||
22 | ITEM | BRAND NO | DESCRIBE | UNIT | QTY | COSTING PRICE | TOTAL | ||
23 | 1 | 1125 | BS 1200R20 G580 JAP | PIECE | 10 | 2,400.000 | 24,000.000 | ||
24 | 2 | 1128 | BS 1200R24 G580 JAP | PIECE | 20 | 2,700.000 | 54,000.000 | ||
25 | 3 | 1129 | GC 1200R20 AZ026 CHI | PIECE | 30 | 1,300.000 | 39,000.000 | ||
26 | 4 | 1123 | BS 750R16 R230 JAP | PIECE | 50 | 400.000 | 20,000.000 | ||
27 | 5 | 1124 | BS 750R16 VSJ JAP | PIECE | 80 | 700.000 | 56,000.000 | ||
28 | 6 | 1126 | BS 315/80R22.5 R184 JAP | PIECE | 100 | 1,900.000 | 190,000.000 | ||
29 | 7 | 1127 | BS 1400R20 VSJ JAP | PIECE | 20 | 3,900.000 | 78,000.000 | ||
30 | 8 | 1130 | GC 1200R20 QAZ183 CHI | PIECE | 30 | 1,400.000 | 42,000.000 | ||
31 | 9 | 1133 | GC 1200R20 QAZ188 CHI | PIECE | 20 | 1,500.000 | 30,000.000 | ||
32 | COSTING TOTAL | 455,000.000 | |||||||
33 | |||||||||
34 | |||||||||
35 | GROUP/COMPANY: BATTERY | ||||||||
36 | ITEM | BRAND NO | DESCRIBE | UNIT | QTY | COSTING PRICE | TOTAL | ||
37 | 1 | 1118 | XPRO 70A L KOR | PIECE | 1 | 300.000 | 300.000 | ||
38 | 2 | 1119 | XPRO 70A R KOR | PIECE | 2 | 250.000 | 500.000 | ||
39 | 3 | 1122 | NOVA 150A L KOR | PIECE | 1 | 700.000 | 700.000 | ||
40 | 4 | 1123 | ASIMCO 150A L KOR | PIECE | 2 | 700.000 | 1,400.000 | ||
41 | 5 | 1120 | XPRO 90A R KOR | PIECE | 2 | 400.000 | 800.000 | ||
42 | 6 | 1121 | HANKOOK 150A L KOR | PIECE | 10 | 800.000 | 8,000.000 | ||
43 | COSTING TOTAL | 11,700.000 | |||||||
COSTING |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G37:G42,G23:G31,G3:G17 | G3 | =F3*E3 |
G18 | G18 | =SUM(G6:G17) |
G32 | G32 | =SUM(G25:G31) |
G43 | G43 | =SUM(G37:G42) |
otherwise when there are dates then split within dates in C3,E3 in SPLIT sheet.
thanks.