abdo meghari
Well-known Member
- Joined
- Aug 3, 2021
- Messages
- 531
- Office Version
- 2019
Hello
I have two sheets contains BRANDS in column C and many duplicates brands contain different prices in column E . so result should show from column I ( CODE,BRAND , PRICE)
the PRICE in header should increment as the brand needs when contains multiple prices.
.
result
data in each sheet could be 5000 rows
the match for each brand is choice for column B or C .
thanks
I have two sheets contains BRANDS in column C and many duplicates brands contain different prices in column E . so result should show from column I ( CODE,BRAND , PRICE)
the PRICE in header should increment as the brand needs when contains multiple prices.
| transfer.xlsm | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 3 | ITEM | CODE | BRAND | QTY | UNIT PRICE | TOTAL | |||||||||
| 4 | 1 | 1241 | BS 1200R20 G580 JAP | 220.00 | 2,035.000 | 447,700.00 | |||||||||
| 5 | 2 | 1244 | BS 1200R20 R187 JAP | 4.00 | 2,000.000 | 8,000.00 | |||||||||
| 6 | 3 | 1269 | BS 1200R24 G580 JAP | 4.00 | 1,900.000 | 7,600.00 | |||||||||
| 7 | 4 | 1556 | BS 185R14C R660 TR | 12.00 | 423.000 | 5,076.00 | |||||||||
| 8 | 5 | 1221 | BS 205/70R15C R623 THI | 72.00 | 405.000 | 29,160.00 | |||||||||
| 9 | 6 | 1547 | BS 205R16C D840 THI | 8.00 | 625.000 | 5,000.00 | |||||||||
| 10 | 7 | 1502 | BS 215/65R16C R611 THI | 4.00 | 600.000 | 2,400.00 | |||||||||
| 11 | 8 | 1502 | BS 215/65R16C R611 THI | 8.00 | 583.000 | 4,664.00 | |||||||||
| 12 | 9 | 1227 | BS 215/70R15C R623 THI | 22.00 | 425.000 | 9,350.00 | |||||||||
| 13 | 10 | 1227 | BS 215/70R15C R623 THI | 28.00 | 544.000 | 15,232.00 | |||||||||
| 14 | 11 | 1503 | BS 225/85R16C R202 JAP | 6.00 | 975.000 | 5,850.00 | |||||||||
| 15 | 12 | 1310 | BS 225/95R16C D618 JAP | 40.00 | 515.000 | 20,600.00 | |||||||||
| 16 | 13 | 1310 | BS 225/95R16C D618 JAP | 50.00 | 695.000 | 34,750.00 | |||||||||
| 17 | 14 | 1310 | BS 225/95R16C D618 JAP | 38.00 | 715.000 | 27,170.00 | |||||||||
| 18 | 15 | 1310 | BS 225/95R16C D618 JAP | 58.00 | 700.000 | 40,600.00 | |||||||||
| 19 | 16 | 1402 | BS 245/70R16 D697 JAP | 4.00 | 590.000 | 2,360.00 | |||||||||
| 20 | 17 | 1346 | BS 255/70R15C D840 | 24.00 | 505.000 | 12,120.00 | |||||||||
| 21 | 18 | 1346 | BS 255/70R15C D840 | 17.00 | 635.000 | 10,795.00 | |||||||||
| 22 | 19 | 1326 | BS 265/60R18 D840 JAP | 36.00 | 721.000 | 25,956.00 | |||||||||
| 23 | 20 | 1534 | BS 265/65R17 D693 THI | 36.00 | 745.000 | 26,820.00 | |||||||||
| 24 | 21 | 1391 | BS 265/65R17 D840 JAP | 5.00 | 690.000 | 3,450.00 | |||||||||
| 25 | 22 | 1391 | BS 265/65R17 D840 JAP | 50.00 | 535.000 | 26,750.00 | |||||||||
| 26 | 23 | 1411 | BS 275/55R20 ALENZA1 JAP | 4.00 | 725.000 | 2,900.00 | |||||||||
| 27 | 24 | 1411 | BS 275/55R20 ALENZA1 JAP | 4.00 | 942.000 | 3,768.00 | |||||||||
| 28 | 25 | 1190 | BS 285/50R20 DSPORT JAP | 1.00 | 705.000 | 705.00 | |||||||||
| 29 | 26 | 1190 | BS 285/50R20 DSPORT JAP | 8.00 | 936.000 | 7,488.00 | |||||||||
| 30 | 27 | 1315 | BS 315/80R22.5 G580 JAP | 12.00 | 2,470.000 | 29,640.00 | |||||||||
| 31 | 28 | 1257 | BS 315/80R22.5 R184 JAP | 37.00 | 2,015.000 | 74,555.00 | |||||||||
| 32 | 29 | 1401 | BS 650R16 R230 JAP | 5.00 | 570.000 | 2,850.00 | |||||||||
| 33 | 30 | 1305 | BS 700R16 R230 JAP | 16.00 | 762.000 | 12,192.00 | |||||||||
| 34 | 31 | 1306 | BS 750R16 R230 JAP | 80.00 | 715.000 | 57,200.00 | |||||||||
| 35 | 32 | 1306 | BS 750R16 R230 JAP | 120.00 | 940.000 | 112,800.00 | |||||||||
| 36 | 33 | 1307 | BS 750R16 VSJ JAP | 5.00 | 910.000 | 4,550.00 | |||||||||
| 37 | 34 | 1284 | GC 1200R20 AZ0026 CHI | 80.00 | 895.000 | 71,600.00 | |||||||||
| 38 | 35 | 1284 | GC 1200R20 AZ0026 CHI | 324.00 | 1,125.000 | 364,500.00 | |||||||||
| 39 | 36 | 1285 | GC 1200R20 AZ0183 CHI | 40.00 | 925.000 | 37,000.00 | |||||||||
| 40 | 37 | 1285 | GC 1200R20 AZ0183 CHI | 10.00 | 1,225.000 | 12,250.00 | |||||||||
| 41 | 38 | 1285 | GC 1200R20 AZ0183 CHI | 140.00 | 1,205.000 | 168,700.00 | |||||||||
| 42 | 39 | 1292 | GC 1200R24 AZ166 CHI | 24.00 | 935.000 | 22,440.00 | |||||||||
| 43 | 40 | 1385 | GC 315/80R22.5 AT161 CHI | 20.00 | 735.000 | 14,700.00 | |||||||||
| 44 | 41 | 1385 | GC 315/80R22.5 AT161 CHI | 10.00 | 955.000 | 9,550.00 | |||||||||
| 45 | 42 | 1287 | GC 315/80R22.5 AZ126 CHI | 60.00 | 735.000 | 44,100.00 | |||||||||
| 46 | 43 | 1294 | GC 315/80R22.5 AZ188 CHI | 20.00 | 745.000 | 14,900.00 | |||||||||
| 47 | 44 | 1294 | GC 315/80R22.5 AZ188 CHI | 24.00 | 965.000 | 23,160.00 | |||||||||
| 48 | 45 | 1492 | GC 385/65R22.5 AT131 CHI | 14.00 | 1,275.000 | 17,850.00 | |||||||||
| 49 | 46 | 1528 | TH 185/65R14 H-93 CHI | 4.00 | 134.000 | 536.00 | |||||||||
| 50 | 47 | 1493 | WL 195/65R15 Z-108 CHI | 4.00 | 173.000 | 692.00 | |||||||||
| 51 | 48 | 1486 | WL 205/55R16 Z-108 CHI | 2.00 | 185.000 | 370.00 | |||||||||
| 52 | TOTAL | 1,814.00 | 1,882,399.00 | ||||||||||||
PURCHASING | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D52,F52 | D52 | =SUM(D4:D51) |
| F4:F51 | F4 | =D4*E4 |
.
| transfer.xlsm | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 3 | ITEM | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
| 4 | 1 | 1306 | BS 750R16 R230 JAP | 10.00 | 775.000 | 7,750.000 | ||
| 5 | 2 | 1306 | BS 750R16 R230 JAP | 4.00 | 780.000 | 3,120.000 | ||
| 6 | 3 | 1305 | BS 700R16 R230 JAP | 2.00 | 770.000 | 1,540.000 | ||
| 7 | TOTAL | 16.00 | 12,410.00 | |||||
SELLING | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D7,F7 | D7 | =SUM(D4:D6) |
| F4:F6 | F4 | =D4*E4 |
result
| transfer.xlsm | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | CODE | BRAND | UNIT PRICE1 | UNIT PRICE2 | UNIT PRICE3 | UNIT PRICE4 | ||||||||||
| 2 | 1241 | BS 1200R20 G580 JAP | 2,035.000 | - | - | - | ||||||||||
| 3 | ITEM | CODE | BRAND | QTY | UNIT PRICE | TOTAL | 1244 | BS 1200R20 R187 JAP | 2,000.000 | - | - | - | ||||
| 4 | 1 | 1241 | BS 1200R20 G580 JAP | 220.00 | 2,035.000 | 447,700.00 | 1269 | BS 1200R24 G580 JAP | 1,900.000 | - | - | - | ||||
| 5 | 2 | 1244 | BS 1200R20 R187 JAP | 4.00 | 2,000.000 | 8,000.00 | 1556 | BS 185R14C R660 TR | 423.000 | - | - | - | ||||
| 6 | 3 | 1269 | BS 1200R24 G580 JAP | 4.00 | 1,900.000 | 7,600.00 | 1221 | BS 205/70R15C R623 THI | 405.000 | - | - | - | ||||
| 7 | 4 | 1556 | BS 185R14C R660 TR | 12.00 | 423.000 | 5,076.00 | 1547 | BS 205R16C D840 THI | 625.000 | - | - | - | ||||
| 8 | 5 | 1221 | BS 205/70R15C R623 THI | 72.00 | 405.000 | 29,160.00 | 1502 | BS 215/65R16C R611 THI | 600.000 | 583.000 | - | - | ||||
| 9 | 6 | 1547 | BS 205R16C D840 THI | 8.00 | 625.000 | 5,000.00 | 1227 | BS 215/70R15C R623 THI | 425.000 | 544.000 | - | - | ||||
| 10 | 7 | 1502 | BS 215/65R16C R611 THI | 4.00 | 600.000 | 2,400.00 | 1503 | BS 225/85R16C R202 JAP | 975.000 | - | - | - | ||||
| 11 | 8 | 1502 | BS 215/65R16C R611 THI | 8.00 | 583.000 | 4,664.00 | 1310 | BS 225/95R16C D618 JAP | 515.000 | 695 | 715 | 700 | ||||
| 12 | 9 | 1227 | BS 215/70R15C R623 THI | 22.00 | 425.000 | 9,350.00 | 1402 | BS 245/70R16 D697 JAP | 590.000 | - | - | - | ||||
| 13 | 10 | 1227 | BS 215/70R15C R623 THI | 28.00 | 544.000 | 15,232.00 | 1346 | BS 255/70R15C D840 | 505.000 | 635.000 | - | - | ||||
| 14 | 11 | 1503 | BS 225/85R16C R202 JAP | 6.00 | 975.000 | 5,850.00 | 1326 | BS 265/60R18 D840 JAP | 721.000 | - | - | - | ||||
| 15 | 12 | 1310 | BS 225/95R16C D618 JAP | 40.00 | 515.000 | 20,600.00 | 1534 | BS 265/65R17 D693 THI | 745.000 | - | - | - | ||||
| 16 | 13 | 1310 | BS 225/95R16C D618 JAP | 50.00 | 695.000 | 34,750.00 | 1391 | BS 265/65R17 D840 JAP | 535.000 | 690.000 | - | - | ||||
| 17 | 14 | 1310 | BS 225/95R16C D618 JAP | 38.00 | 715.000 | 27,170.00 | 1411 | BS 275/55R20 ALENZA1 JAP | 725.000 | 942.000 | - | - | ||||
| 18 | 15 | 1310 | BS 225/95R16C D618 JAP | 58.00 | 700.000 | 40,600.00 | 1190 | BS 285/50R20 DSPORT JAP | 936.000 | 705.000 | - | - | ||||
| 19 | 16 | 1402 | BS 245/70R16 D697 JAP | 4.00 | 590.000 | 2,360.00 | 1315 | BS 315/80R22.5 G580 JAP | 2,470.000 | - | - | - | ||||
| 20 | 17 | 1346 | BS 255/70R15C D840 | 24.00 | 505.000 | 12,120.00 | 1257 | BS 315/80R22.5 R184 JAP | 2,015.000 | - | - | - | ||||
| 21 | 18 | 1346 | BS 255/70R15C D840 | 17.00 | 635.000 | 10,795.00 | 1401 | BS 650R16 R230 JAP | 570.000 | - | - | - | ||||
| 22 | 19 | 1326 | BS 265/60R18 D840 JAP | 36.00 | 721.000 | 25,956.00 | 1305 | BS 700R16 R230 JAP | 762.000 | - | - | - | ||||
| 23 | 20 | 1534 | BS 265/65R17 D693 THI | 36.00 | 745.000 | 26,820.00 | 1306 | BS 750R16 R230 JAP | 715.000 | 940.000 | - | - | ||||
| 24 | 21 | 1391 | BS 265/65R17 D840 JAP | 5.00 | 690.000 | 3,450.00 | 1307 | BS 750R16 VSJ JAP | 910.000 | - | - | - | ||||
| 25 | 22 | 1391 | BS 265/65R17 D840 JAP | 50.00 | 535.000 | 26,750.00 | 1284 | GC 1200R20 AZ0026 CHI | 895.000 | 1,125.000 | - | - | ||||
| 26 | 23 | 1411 | BS 275/55R20 ALENZA1 JAP | 4.00 | 725.000 | 2,900.00 | 1285 | GC 1200R20 AZ0183 CHI | 925.000 | 1,225.000 | 1,205.000 | - | ||||
| 27 | 24 | 1411 | BS 275/55R20 ALENZA1 JAP | 4.00 | 942.000 | 3,768.00 | 1292 | GC 1200R24 AZ166 CHI | 935.000 | - | - | - | ||||
| 28 | 25 | 1190 | BS 285/50R20 DSPORT JAP | 1.00 | 705.000 | 705.00 | 1385 | GC 315/80R22.5 AT161 CHI | 735.000 | 955.000 | - | - | ||||
| 29 | 26 | 1190 | BS 285/50R20 DSPORT JAP | 8.00 | 936.000 | 7,488.00 | 1287 | GC 315/80R22.5 AZ126 CHI | 735.000 | - | - | - | ||||
| 30 | 27 | 1315 | BS 315/80R22.5 G580 JAP | 12.00 | 2,470.000 | 29,640.00 | 1294 | GC 315/80R22.5 AZ188 CHI | 745.000 | 965.000 | - | - | ||||
| 31 | 28 | 1257 | BS 315/80R22.5 R184 JAP | 37.00 | 2,015.000 | 74,555.00 | 1492 | GC 385/65R22.5 AT131 CHI | 1,275.000 | - | - | - | ||||
| 32 | 29 | 1401 | BS 650R16 R230 JAP | 5.00 | 570.000 | 2,850.00 | 1528 | TH 185/65R14 H-93 CHI | 134.000 | - | - | - | ||||
| 33 | 30 | 1305 | BS 700R16 R230 JAP | 16.00 | 762.000 | 12,192.00 | 1493 | WL 195/65R15 Z-108 CHI | 173.000 | - | - | - | ||||
| 34 | 31 | 1306 | BS 750R16 R230 JAP | 80.00 | 715.000 | 57,200.00 | 1486 | WL 205/55R16 Z-108 CHI | 185.000 | - | - | - | ||||
| 35 | 32 | 1306 | BS 750R16 R230 JAP | 120.00 | 940.000 | 112,800.00 | ||||||||||
| 36 | 33 | 1307 | BS 750R16 VSJ JAP | 5.00 | 910.000 | 4,550.00 | ||||||||||
| 37 | 34 | 1284 | GC 1200R20 AZ0026 CHI | 80.00 | 895.000 | 71,600.00 | ||||||||||
| 38 | 35 | 1284 | GC 1200R20 AZ0026 CHI | 324.00 | 1,125.000 | 364,500.00 | ||||||||||
| 39 | 36 | 1285 | GC 1200R20 AZ0183 CHI | 40.00 | 925.000 | 37,000.00 | ||||||||||
| 40 | 37 | 1285 | GC 1200R20 AZ0183 CHI | 10.00 | 1,225.000 | 12,250.00 | ||||||||||
| 41 | 38 | 1285 | GC 1200R20 AZ0183 CHI | 140.00 | 1,205.000 | 168,700.00 | ||||||||||
| 42 | 39 | 1292 | GC 1200R24 AZ166 CHI | 24.00 | 935.000 | 22,440.00 | ||||||||||
| 43 | 40 | 1385 | GC 315/80R22.5 AT161 CHI | 20.00 | 735.000 | 14,700.00 | ||||||||||
| 44 | 41 | 1385 | GC 315/80R22.5 AT161 CHI | 10.00 | 955.000 | 9,550.00 | ||||||||||
| 45 | 42 | 1287 | GC 315/80R22.5 AZ126 CHI | 60.00 | 735.000 | 44,100.00 | ||||||||||
| 46 | 43 | 1294 | GC 315/80R22.5 AZ188 CHI | 20.00 | 745.000 | 14,900.00 | ||||||||||
| 47 | 44 | 1294 | GC 315/80R22.5 AZ188 CHI | 24.00 | 965.000 | 23,160.00 | ||||||||||
| 48 | 45 | 1492 | GC 385/65R22.5 AT131 CHI | 14.00 | 1,275.000 | 17,850.00 | ||||||||||
| 49 | 46 | 1528 | TH 185/65R14 H-93 CHI | 4.00 | 134.000 | 536.00 | ||||||||||
| 50 | 47 | 1493 | WL 195/65R15 Z-108 CHI | 4.00 | 173.000 | 692.00 | ||||||||||
| 51 | 48 | 1486 | WL 205/55R16 Z-108 CHI | 2.00 | 185.000 | 370.00 | ||||||||||
| 52 | TOTAL | 1,814.00 | 1,882,399.00 | |||||||||||||
PURCHASING | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D52,F52 | D52 | =SUM(D4:D51) |
| F4:F51 | F4 | =D4*E4 |
| transfer.xlsm | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | CODE | BRAND | UNIT PRICE1 | UNIT PRICE2 | ||||||||||
| 2 | 1305 | BS 700R16 R230 JAP | 770.000 | - | ||||||||||
| 3 | ITEM | CODE | BRAND | QTY | UNIT PRICE | TOTAL | 1306 | BS 750R16 R230 JAP | 775.000 | 780.000 | ||||
| 4 | 1 | 1306 | BS 750R16 R230 JAP | 10.00 | 775.000 | 7,750.000 | ||||||||
| 5 | 2 | 1306 | BS 750R16 R230 JAP | 4.00 | 780.000 | 3,120.000 | ||||||||
| 6 | 3 | 1305 | BS 700R16 R230 JAP | 2.00 | 770.000 | 1,540.000 | ||||||||
| 7 | TOTAL | 16.00 | 12,410.00 | |||||||||||
| 8 | ||||||||||||||
SELLING | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D7,F7 | D7 | =SUM(D4:D6) |
| F4:F6 | F4 | =D4*E4 |
data in each sheet could be 5000 rows
the match for each brand is choice for column B or C .
thanks
