matching based on first three, last items for the similar items and put into adjacent cell

Hi experts
I hope what I ask for it the Excel can deal with it , despite of it's complicated .so I want matching the items in column B for sheet DATA with column B for sheet REP1 . the problem is very similar for the items with simple differnces . I no know if my standard is acceptable . I want when match the items based on sheet DATA should depend on the first three & last items( because will show in another sheet REP1 with simple differences like space or add another item or the size of letter , then should ignore theses differnces)
for instance
in sheet DATA the item =BS 1200R20 18PR G580 TCF JAP
and the same item =BS 1200R20 G580 TCF JAP in sheet REP1
but becarful when search the items in sheet REP is not necessary could be in the same location into cell as in sheet DATA .the most important should be matched based on first three, last items for the similar item for sheet DATA
so when search based on first three, last items for the similar item for sheet DATA will take theses BS 1200R20 G580 JAP then should be matched and put BS 1200R20 18PR G580 TCF into adjacent cell BS 1200R20 G580 JAP as I put the result in sheet master. also should merge the values for column C & D and add column BALANCE to subtract column C from column D and highlighted the items are simple differences .
should show the whole items for two sheets even if the item is available in sheet but not is vailable in another .
I put a little result in sheet MASTER to understand it
last thing if the items values are zero for columns C,D for for any sheet no need showing in sheet MASTER
if what I ask for it is impossible . all of my ears to any suggestion .
thanks

You have numerous posts on this matching issue so you need to address the problem (as I understand it) of why the same product is recoreded under diffrent "codes".

Take this example:

BS 385/65R22.5 R164 THI vs BS 385/65 R22.5 18PR TCF R164 THI

1. Can we match initially on the first TWO fields as highlighted in BOLD?
2. If the above match is found,
3. Check whole codes (in DATA/REP1) to find EXACT match. e,g BS 315/80R22.5 R184 JAP vs BS 315/80R22.5 R184 JAP
4. If 3. fails, then which of the fields in RED need to be checked to ensure the correct match- last field only ("TUI") or last TWO ("RI64 TUI") if there are 2 (or more) fields?

1. Can we match initially on the first TWO fields as highlighted in BOLD?

I'm not sure if I understand you
does mean just BS 385/65R22.5 without the last item THI ?
if it's so I don't think to correctly showing the item .
for more detailes always the two first and the last item are common with the others items in another sheet . always repeat the first two items when match but the specific standard is the last item for many items .

3. Check whole codes (in DATA/REP1) to find EXACT match. e,g BS 315/80R22.5 R184 JAP vs BS 315/80R22.5 R184 JAP

when find the same thing without any differnces also should put in djacent cells .

If 3. fails, then which of the fields in RED need to be checked to ensure the correct match- last field only ("TUI") or last TWO ("RI64 TUI") if there are 2 (or more) fields?

should be the last field .

What is required (and you know this) is a "Data clean" routine which recognises (or tries to!) the incorrect data.

Example

BS 205/70R15C R623 THI vs BS 205/70 R15C R623 THI i.e a missing blank in the first.

So you need a single source (with you ?) of all (correct) codes and a macro which will run against the data received (manually input from e_mails ?) and hopefully correct all errors.

I edited file. also I would add some detailes if there is new item is existed in one of the sheet but not in another how should be showing .see two last row in sheet master .
I hope this help . just tell me if need any thing
thanks

Perhaps this will be of some help.
1. Select cells B2:B84 on the Rep1 sheet.
2. Utilize the Text to Columns feature selecting > Delimited > Space
3. Set the destination to E2 before selecting Finish
4. Paste the following into cell K2 and copy down:

Formula:

=INDEX(DATA!B$2:B$85,AGGREGATE(15,6,(ROW(DATA!B$2:B$85)-ROW(DATA!B$1))/(ISNUMBER(SEARCH(E2,DATA!B$2:B$85)))/(ISNUMBER(SEARCH(F2,DATA!B$2:B$85)))/(ISNUMBER(SEARCH(INDEX(G2:J2,AGGREGATE(14,6,(COLUMN(G2:J2)-COLUMN(F2))/(G2:J2<>""),1)),DATA!B$2:B$85))),1))

5. For Input use: =SUM(SUMIFS(C$2:C$84,B$2:B$84,B2),SUMIFS(DATA!C$2:C$85,DATA!B$2:B$85,K2))
6. For Balance use: =SUM(SUMIFS(D$2:D$84,B$2:B$84,B2),SUMIFS(DATA!D$2:D$85,DATA!B$2:B$85,K2))
Let us know if you have any questions.

I would assume those to be the ones that appear on the Rep1 sheet and have no match on the Data sheet such as BS 215/70R15C R660 TR which was identified on the Master sheet.
To remove the #NUM! error modify the formula in K2 and down to read:

Formula:

=IFERROR(INDEX(DATA!B$2:B$85,AGGREGATE(15,6,(ROW(DATA!B$2:B$85)-ROW(DATA!B$1))/(ISNUMBER(SEARCH(E2,DATA!B$2:B$85)))/(ISNUMBER(SEARCH(F2,DATA!B$2:B$85)))/(ISNUMBER(SEARCH(INDEX(G2:J2,AGGREGATE(14,6,(COLUMN(G2:J2)-COLUMN(F2))/(G2:J2<>""),1)),DATA!B$2:B$85))),1)),"")

To identify the brands listed on the Data sheet but not on the Rep1 sheet:
1. On the Data sheet populate column E using: =ISERROR(MATCH(B2,'REP1'!K$2:K$84,0))
2. On the Rep1 sheet starting with cell K86 use: =IFERROR(INDEX(DATA!B$2:B$85,AGGREGATE(15,6,(ROW(DATA!B$2:B$85)-ROW(DATA!B$1))/(DATA!E$2:E$85=TRUE),ROWS(K$86:K86))),"")
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.