could extract items based on first & last two items and merge values

A

abdo meghari

Active Member
Joined
Aug 3, 2021
Messages
465
Office Version
  1. 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 .

TIRES (1).xlsm
ABCD
1ITEMBRANDQTYBALANCE
21BS 1200R20 18PR G580 JAP1,147.00791.00
32BS 13R22.5 18PR R187 JAP60.00-
43BS 315/80R22.5 18PR R184 JAP98.00200.00
54BS 315/80R22.5 G580 JAP20.00-
65BS 1400R20 VSJ TCF JAP141.00-
76BS 1200R24 TCF 18PR G580 JAP194.00100.00
87BS 385/65R22.5 R164 JAP70.00-
98BS 385/65R22.5 R164 THI200.00100.00
109BS 1200R24 22PR G582 JAP100.00100.00
1110BS 265/70R16 D840 THI40.0050.00
1211BS 205R16C D840 THI40.0050.00
1312BS 195/65R15 EP150 THI50.00-
1413BS 205/70R15C R623 THI200.00360.00
1514BS 215/70R15C R623 THI100.00-
1615BS 175/70R13 EP150 THI20.00-
1716BS 235/55R17 T005 THI40.00-
1819BS 275/70R16 H005 THI150.00-
1920BS 255/70R15C D840 THI200.00-
2021BS 1200R20 G580 THI-137.00
2122BS 315/80R22.5 G582 THI-50.00
2223BS 175/70R14 EP150 THI-50.00
2324BS 215/50R17 EP300 THI-40.00
2425175/65R14 B250 THI-150.00
2526BS 245/70R16 D684 THI-25.00
2627BS 225/55R17 EP300 THI-20.00
2728BS 205/65R15 T005 THI-93.00
2829BS 205/55R16 T005 THI-50.00
2930BS 195R15C 623 THI-162.00
3031BS 255/70R15C D840 THI-150.00
3132BS 195/55R16 EP300 THI-30.00
3233BS 195R14C R623 THI-16.00
3334BS 205/60R16 T005 THI-150.00
3435BS 255/70R16 H005 THI-30.00
3536BS 215/45R17 EA03 THI-50.00
3637BS 1200R20 R187 TCF JAP-89.00
3738BS 325/95R24 G582 JAP-50.00
3839FS 215/60R16 99V XL IT-329.00
3940BS 700R16 12PR R230 JAP--
4041BS 215/70R15C R623 THI--
4142BS 1400R20VSJ TCF JAP--
4243BS 1400R20 TCF R180 JAP--
4344BS 1400R20 TCF R180BZ JAP--
4445BS 185/65R15 B250 JAP--
4546BS 195R15C 613V JAP--
4647BS 205/65R16 EP300 IND--
4748BS 205/70R15C R623 THI--
4849BS 215/55R16 EP300 IND--
4950BS 215/55R17 T005 JAP--
5051BS 215/70R16 D697 IND--
5152BS 225/45R17 050A JAP--
5253BS 225/50R17 EP300 THI--
5354BS 225/55R16 EP300 IND--
5455BS 225/60R16 EP300 IND--
5556BS 225/70R15C R623 JAP--
5657BS 235/45R17 EA03 THI--
5758BS 235/65R17 D697 IND--
5859BS 235/85R16 D697 IND--
5960BS 245/40R18PR 050A JAP--
6061BS 245/65R17 D689 JAP--
6162BS 245/70R17 684A JAP--
6263BS 255/70R16 H005 THI--
6364BS 255/70R15C D840 THI--
6465BS 265/65R17 D840 JAP--
6566BS265/70R15 D697 IND--
6667BS 275/65R18 AL01 JAP--
6768BS 425/65R22.5 R164 JAP--
6869BS 445/65R22.5 R164 JAP--
6970BS 750R16 R230 TCF JAP--
7071BS 225/55R17 EP300 VIT--
7172BS 185/70R14 B250 THI--
7273BS 315/80R22.5-18PR G582 THI--
7374BS 185/65R14 EP150 IND--
7475BS 185/70R13 EP150 IND--
7576BS 215/65R15 T005 IND--
7677BS 265/70R15 D697 IND--
7778BS 315/80R22.5 R184 THI--
7879DT 385/65R22.5 DT40 THI--
7980BS 195R14C R660 TR--
8081BS 235/65R16C R660 TR--
8182BS 195R15C R660 TR--
8283BS 205/70R15C R660 TR--
8384BS 215/70R15C R660 TR11.00-
DATA



TIRES (1).xlsm
ABCD
1ITEMBRANDQTYBALANCE
21BS 215/70R15C R623 THI100.00-
33BS 315/80R22.5 R184 JAP98.00200.00
42BS 13R22.5 R187 JAP60.00-
54BS 205R16C D840 THI40.0050.00
65BS 195/65R15 EP150 THI50.00-
76BS 205/70R15C R623 THI200.00360.00
87BS 385/65R22.5 R164 JAP70.00-
98BS 385/65R22.5 18PR TCF R164 THI62.00100.00
109BS 1200R24 G582 JAP100.00100.00
1110BS 315/80R22.5 G580 JAP20.00-
1211BS 1400R20 VSJ TCF JAP10.00-
1312BS 1200R24 G580 JAP10.0010.00
1413BS 1200R20 G580 JAP200.0010.00
1514BS 265/70R16 D840 THI40.0050.00
1615BS 175/70R13 EP150 THI20.00-
1716BS 235/55R17 T005 THI40.00-
1817BS 225/70R16 H005 THI150.00-
1918BS 255/70R16 H005 THI95.00-
2019BS 275/70R16 H005 THI150.00-
2120BS 255/70R15C D840 THI200.00-
2220BS 315/80R22.5 G582 THI-50.00
2320BS 175/70R14 EP150 THI-50.00
2420BS 215/50R17 EP300 THI-40.00
2520175/65R14 B250 THI-150.00
2620BS 245/70R16 D684 THI-25.00
2720BS 225/55R17 EP300 THI-20.00
2820BS 205/65R15 T005 THI-93.00
2920BS 205/55R16 T005 THI-50.00
3020BS 195R15C R623 THI-162.00
3120BS 255/70R15C D840 THI-150.00
3220BS 195/55R16 EP300 THI-30.00
3320BS 195R14C R623 THI-16.00
3420BS 205/60R16 T005 THI-150.00
3520BS 255/70R16 H005 THI-30.00
3620BS 215/45 R17 EA03 THI-50.00
3720BS 1200R20 R187 TCF JAP-89.00
3820BS 325/95R24 G582 JAP-50.00
3920FS 215/60R16 99V XL IT-329.00
4020BS 700R16 12PR R230 JAP--
4120BS 215/70R15C R623 THI--
4220BS 1400R20VSJ TCF JAP--
4320BS 1400R20 TCF R180 JAP--
4420BS 1400R20 TCF R180BZ JAP--
4520BS 185/65R15 B250 JAP--
4620BS 195R15C 613V JAP--
4720BS 205/65R16 EP300 IND--
4820BS 205/70R15C R623 THI--
4920BS 215/55R16 EP300 IND--
5020BS 215/55R17 T005 JAP--
5120BS 215/70R16 D697 IND--
5220BS 225/45R17 050A JAP--
5320BS 225/50R17 EP300 THI--
5420BS 225/70R15C R623 JAP--
5520BS 235/85R16 D697 IND--
5620BS 245/40R18PR 050A JAP--
5720BS 245/65R17 D689 JAP--
5820BS 245/70R17 684A JAP--
5920BS 255/70R16 H005 THI--
6020BS 255/70R15C D840 THI--
6120BS 265/65R17 D840 JAP--
6220BS265/70R15 D697 IND--
6320BS 275/65R18 AL01 JAP--
6420BS 425/65R22.5 R164 JAP--
6520BS 445/65R22.5 R164 JAP--
6620BS 750R16 R230 TCF JAP--
6720BS 1200R20 18PR G580 THI--
6820BS 225/55R17 EP300 VIT--
6920BS 185/70R14 B250 THI--
7020BS 315/80R22.5-18PR G582 TCF THI--
7120BS 185/65R14 EP150 IND--
7220BS 185/70R13 EP150 IND--
7320BS 215/65R15 T005 IND--
7420BS 265/70R15 D697 IND--
7520BS 315/80R22.5 R184 THI--
7620DT 385/65R22.5 DT40 18PR THI--
7720BS 195R14C R660 TR--
7820BS 235/65R16C R660 TR--
7920BS 235/65R16C R660 IT11.00-
8020BS 195R15C R660 TR--
8120BS 205/70R15C R660 TR--
8220BS 215/70R15C R660 IT-11.00
REP1





result

TIRES (1).xlsm
ABCDEF
1ITEMLIST1LIST2INPUTOUTPUTBALANCE
21BS 1200R20 18PR G580 JAPBS 1200R20 G580 JAP1,347.00801.00546.00
32BS 13R22.5 18PR R187 JAPBS 13R22.5 R187 JAP120.000.00120.00
43BS 315/80R22.5 18PR R184 JAPBS 315/80R22.5 R184 JAP196.00400.00-204.00
54BS 315/80R22.5 G580 JAPBS 315/80R22.5 G580 JAP40.0020.0020.00
65BS 1400R20VSJ TCF JAPBS 1400R20 VSJ TCF JAP10.000.0010.00
76BS 1200R24 TCF 18PR G580 JAPBS 1200R24 G580 JAP204.00110.0094.00
87BS 385/65R22.5 R164 JAPBS 385/65R22.5 R164 JAP140.000.00140.00
98BS 385/65R22.5 R164 THIBS 385/65R22.5 18PR TCF R164 THI362.00200.00162.00
109BS 1200R24 22PR G582 JAPBS 1200R24 G582 JAP200.00200.000.00
1110BS 215/70R15C R660 TR-11.000.0011.00
1211-BS 215/70R15C R660 IT0.0011.00-11.00
MASTER
Cell Formulas
RangeFormula
F2:F12F2=D2-E2


I hope to covered all of detailes without any confusing .
thanks
 
Last edited:
Sort by date Sort by votes
you can point to the letter "i" in
Click to expand...
sorry How can do that?

when reach from the first time the I=1 but when reach I=2 will pop error as in picture .
1.PNG
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
All the "-" in column C:D are text string, not zero value
Try to replace it with 0
(Ctrl-H)
then code should works.
 
Upvote 0
sorry this is my bad!

just last thing can I highlight the new item in all of sheets . I mean what is existed in sheet DATA but nit existed in sheet REP1 and what is existed in sheet REP1 and not existed in sheet DATA also when create report in sheet Master should show the highlighted items . actually I mentioned in OP I no know if you saw it .

thanks again
 
Upvote 0
abdo meghari said:
sorry this is my bad!

just last thing can I highlight the new item in all of sheets . I mean what is existed in sheet DATA but nit existed in sheet REP1 and what is existed in sheet REP1 and not existed in sheet DATA also when create report in sheet Master should show the highlighted items . actually I mentioned in OP I no know if you saw it .

thanks again
Click to expand...
If it is not very important, you can see they are few rows at the bottom, without the sequence number in column A.
 
Upvote 0
I forgot to mention about autonumber in column A to the bottom . there is some items doesn't contains numbers .

just arrange data not more and not less .as to highlight it's important to see the different items between two sheets (DATA,REP1)

if you can't modify the code I will satisfy the code. it's great!! and nobody see my thread I appreciate your help.

thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
rearranging columns by reversing in location and create headers
2 3
Replies
20
Views
221
Alex Blakenburg
A
A
  • Question
Highlight and delete duplicates items in the different spaces for multiple columns
Replies
1
Views
309
abdo meghari
A
A
  • Solved
auto fill for two lists under last duplicates items based on matching column
2
Replies
14
Views
600
Legacy 143009
L
A
  • Solved
replace column with another based on match column between two sheets
Replies
7
Views
385
JoeMo
J
A
  • Solved
add message & disable the code based on condition
2
Replies
16
Views
732
abdo meghari
A

Forum statistics

Threads
1,214,798
Messages
6,121,635
Members
449,043
Latest member
farhansadik

Share this page

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