delete unmatched item for two sheets with another two sheets and put the similar to adjacent

A

abdo meghari

Well-known Member
Joined
Aug 3, 2021
Messages
612
Office Version
  1. 2019
hello

I have sheets (TIRES,TIRES1,FS,BS) should match sheet TIRES for column B with sheet FS if the items are existed in sheet TIRES but not existed in sheet FS then should delete them from sheet TIRES and put the similar items into adjacent cells for sheet TIRES based on sheet FS . the same thing for sheet TIRES1, BS
list1.xlsx
AB
1ITEMBRAND
21BS 175/70R13 B25 INDO
32BS 175/70R13 EP150 THI
43BS 195/60R15 AR20 INDO
54BS 195/60R15 EP150 THI
65BS 195/60R15 T001 JAP
76BS 195/60R15 150EZ THI
87BS 195/65R15 MY02 THI
98BS 205/70R15 694 JAP
109BS 185/70R13 EP150 INDO
1110BS 175/65R14 EP150 THI
1211BS 175/65R14 B25 INDO
1312BS 175/70R14 EP150 THI
1413BS 175/70R14 MY02 THI
1514BS 185/65R14 TEC THI
1615BS 185/65R14 150EZ INDO
1716BS 185/65R14 EP150 INDO
1817BS 195/70R14 150EZ INDO
1918BS 185/65R15 TC10 INDO
2019BS 185/65R15 T005 INDO
2120BS 185/65R15 T01 JAP
2221BS 185/65R15 B250 JAP
2322FS 205/55R16 RE003 THI
2423FS 205/65R15 EP150 INDO
2524FS 205/65R15 MY02 THI
2625FS 195/65R15 EP150 THI
2726FS 195/65R15 EP150 JAP
2827FS 195/65R15 T001 JAP
2928FS 195/55R16 EP300 THI
3029FS 205/65R15 TZ700 JAP
TIRES





list1.xlsx
AB
1ITEMBRAND
21BS 175/70R13 B25 INDO
32BS 175/70R13 EP150 THI
43BS 195/60R15 AR20 INDO
54BS 195/60R15 EP150 THI
65BS 195/60R15 T001 JAP
76BS 195/60R15 150EZ THI
87BS 195/65R15 MY02 THI
98BS 205/70R15 694 JAP
109BS 185/70R13 EP150 INDO
1110BS 175/65R14 EP150 THI
1211BS 175/65R14 B25 INDO
1312BS 175/70R14 EP150 THI
1413BS 175/70R14 MY02 THI
1514BS 185/65R14 TEC THI
1615BS 185/65R14 150EZ INDO
1716BS 185/65R14 EP150 INDO
1817BS 195/70R14 150EZ INDO
1918BS 185/65R15 TC10 INDO
2019BS 185/65R15 T005 INDO
2120BS 185/65R15 T01 JAP
2221BS 185/65R15 B250 JAP
2322FS 205/55R16 RE003 THI
2423FS 205/65R15 EP150 INDO
2524FS 205/65R15 MY02 THI
2625FS 195/65R15 EP150 THI
2726FS 195/65R15 EP150 JAP
2827FS 195/65R15 T001 JAP
2928FS 195/55R16 EP300 THI
3029FS 205/65R15 TZ700 JAP
TIRES1



list1.xlsx
ABC
1ITEMBRANDQTY
21FS 195/65R15 EP150 THI27410
32FS 195/65R15 EP150 JAP28445
43FS 195/65R15 T001 JAP29480
54FS 205/65R15 EP150 INDO25340
65FS 205/65R15 MY02 THI26375
76FS 205/65R15 TZ700 JAP31550
87FS 195/55R16 EP300 THI30515
98FS 205/55R16 RE003 THI24305
FS



list1.xlsx
ABC
1ITEMBRANDQTY
21BS 185/65R15 B250 JAP18095
32BS 195/60R15 AR20 INDO2300
43BS 195/60R15 EP150 THI500
54BS 195/60R15 T001 JAP1535
65BS 195/60R15 150EZ THI2570
76BS 195/65R15 MY02 THI3605
87BS 205/70R15 694 JAP4640
98BS 185/65R14 TEC THI10850
109BS 185/65R14 150EZ INDO11885
1110BS 185/65R14 EP150 INDO12920
BS




result
list1.xlsx
AB
1ITEMBRAND
21FS 195/65R15 EP150 THI
32FS 195/65R15 EP150 JAP
43FS 195/65R15 T001 JAP
54FS 205/65R15 EP150 INDO
65FS 205/65R15 MY02 THI
76FS 205/65R15 TZ700 JAP
87FS 195/55R16 EP300 THI
98FS 205/55R16 RE003 THI
TIRES


list1.xlsx
AB
1ITEMBRAND
21BS 185/65R15 B250 JAP
32BS 195/60R15 AR20 INDO
43BS 195/60R15 EP150 THI
54BS 195/60R15 T001 JAP
65BS 195/60R15 150EZ THI
76BS 195/65R15 MY02 THI
87BS 205/70R15 694 JAP
98BS 185/65R14 TEC THI
109BS 185/65R14 150EZ INDO
1110BS 185/65R14 EP150 INDO
TIRES1
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
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
756
Alex Blakenburg
A
A
  • Solved
add specific character in the beginning of item into column
Replies
9
Views
616
Fluff
Fluff
A
  • Solved
replace item for range with item for Table based on column
2
Replies
13
Views
910
Legacy 143009
L
A
  • Question Question
insert column to auto-number for each item into two sheets
Replies
0
Views
301
abdo meghari
A
A
  • Question Question
split data from sheet into multiple sheets based on first two letters
Replies
5
Views
483
abdo meghari
A

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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