abdo meghari
Well-known Member
- Joined
- Aug 3, 2021
- Messages
- 651
- Office Version
- 2019
Hi experts
I have two sheets(DATA,REP1) each sheet could be 7800 rows .
every sheet contains items in column B and values into columns C,D should match between two sheets based on column B if the ftwo first items and the two last items are the same thing between two sheets thien should fill the whole items for sheet DATA in columnB in sheet MASTER, and the the whole items for sheet REP1 in column C in sheet MASTER( put each matched item into adjacent cells , and should merge values for column D,E and subtract thae values column D from E as show in column F in sheet MASTER based on adjacent cells in column B,C for each item) . if there is item in sheet DATA and is not existed in sheet REP1, then it considers new item and should highlight by red and show by red in sheet MASTER , also if there is item in sheet REP1 and is not existed in sheet DATA , then it considers new item and should highlight by red and show by red in sheet MASTER
and will change and add data every time in sheets DATA & REP should also updtate automatically by replace data(it meansto clear data in sheet MASTER before bring the data)
let's take example
sheet DATA sheet REP
BS 1200R20 18PR G580 JAP BS 1200R20 G580 JAP
as you see the first two items (BS,1200R20) and the last two items (G580,JAP) are the same thing , then should add in column B,C next to each other of them as in row two and merge the values . last thing if any new item whether in sheet DATA or REP1
contains zero values for two columns C,D together then should n't show in sheet MASTER at all. always when match the item usually contains four or five or six items .....
sometimes can contain three items then can't match based on two first & two last items ,then will be new item directly .
result
I hope to covered all of detailes without any confusing .
thanks
I have two sheets(DATA,REP1) each sheet could be 7800 rows .
every sheet contains items in column B and values into columns C,D should match between two sheets based on column B if the ftwo first items and the two last items are the same thing between two sheets thien should fill the whole items for sheet DATA in columnB in sheet MASTER, and the the whole items for sheet REP1 in column C in sheet MASTER( put each matched item into adjacent cells , and should merge values for column D,E and subtract thae values column D from E as show in column F in sheet MASTER based on adjacent cells in column B,C for each item) . if there is item in sheet DATA and is not existed in sheet REP1, then it considers new item and should highlight by red and show by red in sheet MASTER , also if there is item in sheet REP1 and is not existed in sheet DATA , then it considers new item and should highlight by red and show by red in sheet MASTER
and will change and add data every time in sheets DATA & REP should also updtate automatically by replace data(it meansto clear data in sheet MASTER before bring the data)
let's take example
sheet DATA sheet REP
BS 1200R20 18PR G580 JAP BS 1200R20 G580 JAP
as you see the first two items (BS,1200R20) and the last two items (G580,JAP) are the same thing , then should add in column B,C next to each other of them as in row two and merge the values . last thing if any new item whether in sheet DATA or REP1
contains zero values for two columns C,D together then should n't show in sheet MASTER at all. always when match the item usually contains four or five or six items .....
sometimes can contain three items then can't match based on two first & two last items ,then will be new item directly .
TIRES (1).xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ITEM | BRAND | QTY | BALANCE | ||
2 | 1 | BS 1200R20 18PR G580 JAP | 1,147.00 | 791.00 | ||
3 | 2 | BS 13R22.5 18PR R187 JAP | 60.00 | - | ||
4 | 3 | BS 315/80R22.5 18PR R184 JAP | 98.00 | 200.00 | ||
5 | 4 | BS 315/80R22.5 G580 JAP | 20.00 | - | ||
6 | 5 | BS 1400R20 VSJ TCF JAP | 141.00 | - | ||
7 | 6 | BS 1200R24 TCF 18PR G580 JAP | 194.00 | 100.00 | ||
8 | 7 | BS 385/65R22.5 R164 JAP | 70.00 | - | ||
9 | 8 | BS 385/65R22.5 R164 THI | 200.00 | 100.00 | ||
10 | 9 | BS 1200R24 22PR G582 JAP | 100.00 | 100.00 | ||
11 | 10 | BS 265/70R16 D840 THI | 40.00 | 50.00 | ||
12 | 11 | BS 205R16C D840 THI | 40.00 | 50.00 | ||
13 | 12 | BS 195/65R15 EP150 THI | 50.00 | - | ||
14 | 13 | BS 205/70R15C R623 THI | 200.00 | 360.00 | ||
15 | 14 | BS 215/70R15C R623 THI | 100.00 | - | ||
16 | 15 | BS 175/70R13 EP150 THI | 20.00 | - | ||
17 | 16 | BS 235/55R17 T005 THI | 40.00 | - | ||
18 | 19 | BS 275/70R16 H005 THI | 150.00 | - | ||
19 | 20 | BS 255/70R15C D840 THI | 200.00 | - | ||
20 | 21 | BS 1200R20 G580 THI | - | 137.00 | ||
21 | 22 | BS 315/80R22.5 G582 THI | - | 50.00 | ||
22 | 23 | BS 175/70R14 EP150 THI | - | 50.00 | ||
23 | 24 | BS 215/50R17 EP300 THI | - | 40.00 | ||
24 | 25 | 175/65R14 B250 THI | - | 150.00 | ||
25 | 26 | BS 245/70R16 D684 THI | - | 25.00 | ||
26 | 27 | BS 225/55R17 EP300 THI | - | 20.00 | ||
27 | 28 | BS 205/65R15 T005 THI | - | 93.00 | ||
28 | 29 | BS 205/55R16 T005 THI | - | 50.00 | ||
29 | 30 | BS 195R15C 623 THI | - | 162.00 | ||
30 | 31 | BS 255/70R15C D840 THI | - | 150.00 | ||
31 | 32 | BS 195/55R16 EP300 THI | - | 30.00 | ||
32 | 33 | BS 195R14C R623 THI | - | 16.00 | ||
33 | 34 | BS 205/60R16 T005 THI | - | 150.00 | ||
34 | 35 | BS 255/70R16 H005 THI | - | 30.00 | ||
35 | 36 | BS 215/45R17 EA03 THI | - | 50.00 | ||
36 | 37 | BS 1200R20 R187 TCF JAP | - | 89.00 | ||
37 | 38 | BS 325/95R24 G582 JAP | - | 50.00 | ||
38 | 39 | FS 215/60R16 99V XL IT | - | 329.00 | ||
39 | 40 | BS 700R16 12PR R230 JAP | - | - | ||
40 | 41 | BS 215/70R15C R623 THI | - | - | ||
41 | 42 | BS 1400R20VSJ TCF JAP | - | - | ||
42 | 43 | BS 1400R20 TCF R180 JAP | - | - | ||
43 | 44 | BS 1400R20 TCF R180BZ JAP | - | - | ||
44 | 45 | BS 185/65R15 B250 JAP | - | - | ||
45 | 46 | BS 195R15C 613V JAP | - | - | ||
46 | 47 | BS 205/65R16 EP300 IND | - | - | ||
47 | 48 | BS 205/70R15C R623 THI | - | - | ||
48 | 49 | BS 215/55R16 EP300 IND | - | - | ||
49 | 50 | BS 215/55R17 T005 JAP | - | - | ||
50 | 51 | BS 215/70R16 D697 IND | - | - | ||
51 | 52 | BS 225/45R17 050A JAP | - | - | ||
52 | 53 | BS 225/50R17 EP300 THI | - | - | ||
53 | 54 | BS 225/55R16 EP300 IND | - | - | ||
54 | 55 | BS 225/60R16 EP300 IND | - | - | ||
55 | 56 | BS 225/70R15C R623 JAP | - | - | ||
56 | 57 | BS 235/45R17 EA03 THI | - | - | ||
57 | 58 | BS 235/65R17 D697 IND | - | - | ||
58 | 59 | BS 235/85R16 D697 IND | - | - | ||
59 | 60 | BS 245/40R18PR 050A JAP | - | - | ||
60 | 61 | BS 245/65R17 D689 JAP | - | - | ||
61 | 62 | BS 245/70R17 684A JAP | - | - | ||
62 | 63 | BS 255/70R16 H005 THI | - | - | ||
63 | 64 | BS 255/70R15C D840 THI | - | - | ||
64 | 65 | BS 265/65R17 D840 JAP | - | - | ||
65 | 66 | BS265/70R15 D697 IND | - | - | ||
66 | 67 | BS 275/65R18 AL01 JAP | - | - | ||
67 | 68 | BS 425/65R22.5 R164 JAP | - | - | ||
68 | 69 | BS 445/65R22.5 R164 JAP | - | - | ||
69 | 70 | BS 750R16 R230 TCF JAP | - | - | ||
70 | 71 | BS 225/55R17 EP300 VIT | - | - | ||
71 | 72 | BS 185/70R14 B250 THI | - | - | ||
72 | 73 | BS 315/80R22.5-18PR G582 THI | - | - | ||
73 | 74 | BS 185/65R14 EP150 IND | - | - | ||
74 | 75 | BS 185/70R13 EP150 IND | - | - | ||
75 | 76 | BS 215/65R15 T005 IND | - | - | ||
76 | 77 | BS 265/70R15 D697 IND | - | - | ||
77 | 78 | BS 315/80R22.5 R184 THI | - | - | ||
78 | 79 | DT 385/65R22.5 DT40 THI | - | - | ||
79 | 80 | BS 195R14C R660 TR | - | - | ||
80 | 81 | BS 235/65R16C R660 TR | - | - | ||
81 | 82 | BS 195R15C R660 TR | - | - | ||
82 | 83 | BS 205/70R15C R660 TR | - | - | ||
83 | 84 | BS 215/70R15C R660 TR | 11.00 | - | ||
DATA |
TIRES (1).xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ITEM | BRAND | QTY | BALANCE | ||
2 | 1 | BS 215/70R15C R623 THI | 100.00 | - | ||
3 | 3 | BS 315/80R22.5 R184 JAP | 98.00 | 200.00 | ||
4 | 2 | BS 13R22.5 R187 JAP | 60.00 | - | ||
5 | 4 | BS 205R16C D840 THI | 40.00 | 50.00 | ||
6 | 5 | BS 195/65R15 EP150 THI | 50.00 | - | ||
7 | 6 | BS 205/70R15C R623 THI | 200.00 | 360.00 | ||
8 | 7 | BS 385/65R22.5 R164 JAP | 70.00 | - | ||
9 | 8 | BS 385/65R22.5 18PR TCF R164 THI | 62.00 | 100.00 | ||
10 | 9 | BS 1200R24 G582 JAP | 100.00 | 100.00 | ||
11 | 10 | BS 315/80R22.5 G580 JAP | 20.00 | - | ||
12 | 11 | BS 1400R20 VSJ TCF JAP | 10.00 | - | ||
13 | 12 | BS 1200R24 G580 JAP | 10.00 | 10.00 | ||
14 | 13 | BS 1200R20 G580 JAP | 200.00 | 10.00 | ||
15 | 14 | BS 265/70R16 D840 THI | 40.00 | 50.00 | ||
16 | 15 | BS 175/70R13 EP150 THI | 20.00 | - | ||
17 | 16 | BS 235/55R17 T005 THI | 40.00 | - | ||
18 | 17 | BS 225/70R16 H005 THI | 150.00 | - | ||
19 | 18 | BS 255/70R16 H005 THI | 95.00 | - | ||
20 | 19 | BS 275/70R16 H005 THI | 150.00 | - | ||
21 | 20 | BS 255/70R15C D840 THI | 200.00 | - | ||
22 | 20 | BS 315/80R22.5 G582 THI | - | 50.00 | ||
23 | 20 | BS 175/70R14 EP150 THI | - | 50.00 | ||
24 | 20 | BS 215/50R17 EP300 THI | - | 40.00 | ||
25 | 20 | 175/65R14 B250 THI | - | 150.00 | ||
26 | 20 | BS 245/70R16 D684 THI | - | 25.00 | ||
27 | 20 | BS 225/55R17 EP300 THI | - | 20.00 | ||
28 | 20 | BS 205/65R15 T005 THI | - | 93.00 | ||
29 | 20 | BS 205/55R16 T005 THI | - | 50.00 | ||
30 | 20 | BS 195R15C R623 THI | - | 162.00 | ||
31 | 20 | BS 255/70R15C D840 THI | - | 150.00 | ||
32 | 20 | BS 195/55R16 EP300 THI | - | 30.00 | ||
33 | 20 | BS 195R14C R623 THI | - | 16.00 | ||
34 | 20 | BS 205/60R16 T005 THI | - | 150.00 | ||
35 | 20 | BS 255/70R16 H005 THI | - | 30.00 | ||
36 | 20 | BS 215/45 R17 EA03 THI | - | 50.00 | ||
37 | 20 | BS 1200R20 R187 TCF JAP | - | 89.00 | ||
38 | 20 | BS 325/95R24 G582 JAP | - | 50.00 | ||
39 | 20 | FS 215/60R16 99V XL IT | - | 329.00 | ||
40 | 20 | BS 700R16 12PR R230 JAP | - | - | ||
41 | 20 | BS 215/70R15C R623 THI | - | - | ||
42 | 20 | BS 1400R20VSJ TCF JAP | - | - | ||
43 | 20 | BS 1400R20 TCF R180 JAP | - | - | ||
44 | 20 | BS 1400R20 TCF R180BZ JAP | - | - | ||
45 | 20 | BS 185/65R15 B250 JAP | - | - | ||
46 | 20 | BS 195R15C 613V JAP | - | - | ||
47 | 20 | BS 205/65R16 EP300 IND | - | - | ||
48 | 20 | BS 205/70R15C R623 THI | - | - | ||
49 | 20 | BS 215/55R16 EP300 IND | - | - | ||
50 | 20 | BS 215/55R17 T005 JAP | - | - | ||
51 | 20 | BS 215/70R16 D697 IND | - | - | ||
52 | 20 | BS 225/45R17 050A JAP | - | - | ||
53 | 20 | BS 225/50R17 EP300 THI | - | - | ||
54 | 20 | BS 225/70R15C R623 JAP | - | - | ||
55 | 20 | BS 235/85R16 D697 IND | - | - | ||
56 | 20 | BS 245/40R18PR 050A JAP | - | - | ||
57 | 20 | BS 245/65R17 D689 JAP | - | - | ||
58 | 20 | BS 245/70R17 684A JAP | - | - | ||
59 | 20 | BS 255/70R16 H005 THI | - | - | ||
60 | 20 | BS 255/70R15C D840 THI | - | - | ||
61 | 20 | BS 265/65R17 D840 JAP | - | - | ||
62 | 20 | BS265/70R15 D697 IND | - | - | ||
63 | 20 | BS 275/65R18 AL01 JAP | - | - | ||
64 | 20 | BS 425/65R22.5 R164 JAP | - | - | ||
65 | 20 | BS 445/65R22.5 R164 JAP | - | - | ||
66 | 20 | BS 750R16 R230 TCF JAP | - | - | ||
67 | 20 | BS 1200R20 18PR G580 THI | - | - | ||
68 | 20 | BS 225/55R17 EP300 VIT | - | - | ||
69 | 20 | BS 185/70R14 B250 THI | - | - | ||
70 | 20 | BS 315/80R22.5-18PR G582 TCF THI | - | - | ||
71 | 20 | BS 185/65R14 EP150 IND | - | - | ||
72 | 20 | BS 185/70R13 EP150 IND | - | - | ||
73 | 20 | BS 215/65R15 T005 IND | - | - | ||
74 | 20 | BS 265/70R15 D697 IND | - | - | ||
75 | 20 | BS 315/80R22.5 R184 THI | - | - | ||
76 | 20 | DT 385/65R22.5 DT40 18PR THI | - | - | ||
77 | 20 | BS 195R14C R660 TR | - | - | ||
78 | 20 | BS 235/65R16C R660 TR | - | - | ||
79 | 20 | BS 235/65R16C R660 IT | 11.00 | - | ||
80 | 20 | BS 195R15C R660 TR | - | - | ||
81 | 20 | BS 205/70R15C R660 TR | - | - | ||
82 | 20 | BS 215/70R15C R660 IT | - | 11.00 | ||
REP1 |
result
TIRES (1).xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | ITEM | LIST1 | LIST2 | INPUT | OUTPUT | BALANCE | ||
2 | 1 | BS 1200R20 18PR G580 JAP | BS 1200R20 G580 JAP | 1,347.00 | 801.00 | 546.00 | ||
3 | 2 | BS 13R22.5 18PR R187 JAP | BS 13R22.5 R187 JAP | 120.00 | 0.00 | 120.00 | ||
4 | 3 | BS 315/80R22.5 18PR R184 JAP | BS 315/80R22.5 R184 JAP | 196.00 | 400.00 | -204.00 | ||
5 | 4 | BS 315/80R22.5 G580 JAP | BS 315/80R22.5 G580 JAP | 40.00 | 20.00 | 20.00 | ||
6 | 5 | BS 1400R20VSJ TCF JAP | BS 1400R20 VSJ TCF JAP | 10.00 | 0.00 | 10.00 | ||
7 | 6 | BS 1200R24 TCF 18PR G580 JAP | BS 1200R24 G580 JAP | 204.00 | 110.00 | 94.00 | ||
8 | 7 | BS 385/65R22.5 R164 JAP | BS 385/65R22.5 R164 JAP | 140.00 | 0.00 | 140.00 | ||
9 | 8 | BS 385/65R22.5 R164 THI | BS 385/65R22.5 18PR TCF R164 THI | 362.00 | 200.00 | 162.00 | ||
10 | 9 | BS 1200R24 22PR G582 JAP | BS 1200R24 G582 JAP | 200.00 | 200.00 | 0.00 | ||
11 | 10 | BS 215/70R15C R660 TR | - | 11.00 | 0.00 | 11.00 | ||
12 | 11 | - | BS 215/70R15C R660 IT | 0.00 | 11.00 | -11.00 | ||
MASTER |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F12 | F2 | =D2-E2 |
I hope to covered all of detailes without any confusing .
thanks
Last edited: