Match between two sheets and bring missed elements based on column and arrange again

A

abdo meghari

Active Member
Joined
Aug 3, 2021
Messages
471
Office Version
  1. 2019
hi

I have two sheets what I want macro match elements are existed in columns B,C,D for each category in column A so in sheet1 the category for elemnts are missed in column A.

what I want to bring missed categorey from sheet2 to sheet1 and arrange the data based on sheet2 again . about the category begins from first cell and end to last row is LTT then start new category in sheet2 .
sheet1
catogerySizePatternOrigin
195/60R15EP150THI
195/60R15T001JAP
195/65R15MY02THI
195/65R15EP150THI
195/65R15EP150JAP
195/55R16EP300THI
205/55R16RE003THI
205/55R16RE003THI
205/65R15EP150INDO
205/65R15MY02THI
205/65R15T01JAP
FS 205/65R15TZ700JAP
215/65R15T005INDO
205/70R15694JAP
225/75R15CT697INDO
235/95R15CD618JAP
255/70R15CD840THI
205R16CD840THI
205/R16CD697THI
205/55R16AR20INDO
215/45R17T001JAP
215/50R17EP300THI
215/55R17GR90INDO
215/55R17T001JAP
215/55R17T005JAP
215/55R17TECTHI
215/55R17MY02THI
FS 215/55R17TZ700JAP
225/45R17RE050AJAP
225/45R17RE003JAP
FS 225/45R17TZ700JAP
FS 225/55R17TZ700JAP
225/55R17EP300THI
225/55R17D694JAP
225/60R17D-SPORTJAP
225/65R17T697INDO
225/70R17D697THI
235/55R17ER30JAP
235/55R17T005THI
235/55R17D400JAP
235/60R17D400JAP
235/65R17T697INDO
245/45R17T05AJAP
245/65R17D698JAP
245/65R17D697INDO
245/70R17D684JAP
245/75R17693AJAP
265/65R17D840JAP
285/65R17R683JAP
225/35R19RE050JAP
225/40R18GR90JAP
225/40R18GR90INDO
225/45R18AR20INDO
235/50R18ALENZAJAP
235/55R18E031JAP
235/60R18D-SPORTJAP
245/40R18RE050AJAP
245/40R19T005JAP
245/40ZR20S001JAP
245/45R18T001JAP
245/45R19T005JAP
255/35R18GR90JAP
255/40R19S001JAP
255/45R20DSPORTJAP
265/60R18D840JAP
265/70R18D684JAP
275/35R18RE050AJAP
275/40R18RE050AJAP
275/40R18S007JAP
275/40R19S007JAP
275/40R20DSPORTJAP
275/45R19ALENZAJAP
275/45R20DSPORTJAP
275/55R17DSPORTJAP
275/55R20ALENZAJAP
285/50R20DSPORT
275/65R18116HTHI
285/60R18T01JAP
285/60R18D850JAP
245/40R20RE050JAP
265/50R20SPORTJAP
275/30R20RE050JAP
275/40R20DHPJAP
275/55R20D680JAP
285/30R20RE0050AJAP
315/35R20SPORTJAP
205/55R16G3JAP
205/55R16T001
205/55R16ER300POL
205/60R16AR20INDO
205/60R16T005THI
205/60R16T001JAP
205/65R16EP300INDO
215/55R16EP300INDO
215/60R16EP300THI
215/60R16EP200THI
215/60R16T005THI
215/60R16AR20INDO
215/65R16AR20INDO
225/55R16EP300INDO
225/55R16T001JAP
225/60R16AR20INDO
225/60R16EP300JAP
225/60R16T001JAP
225/70R16D687JAP
225/70R16H005THI
225/75R16T697INDO
235/60R16T01JAP
235/60R16ALENZAJAP
245/70R16D684THI
255/70R16T697INDO
255/70R16H005THI
265/70R16D850THI
265/70R16D840THI
275/70R16H005THI
275/70R16D694JAP
LT285/75R16AT001JAP
650R16R230
700R16R23012PR
750R16R230
155R12CR624INDO
155R12CR623INDO
165R13CR624INDO
185R14C613VJAP
185R14CR624INDO
195R14CR624INDO
195R14C613VJAP
195R14CR623THI
205R14C613VJAP
205R14CR624INDO
215R14CR624INDO
195R15C613VJAP
195R15CR623THI
195/70R15CR623JAP
195/75R16C613VJAP
205/70R15CR624INDO
205/70R15CR623THI
205/70R15CR623JAP
215/65R16CR624INDO
215/65R16CR611THI
215/70R15CR624INDO
215/70R15CR623THI
225/70R15CR623JAP
750R16VSJ8PR
825R16R180JAP
1200R20G580JAP
1200R20R187JAP
1200R20G580THI
1200R20R187THI
1200R20M802THI
Dayton 1200R24DA53THI
FS1200R20FSR4000JAP
1200R24G580JAP
1200R24G582JAP
FS1200R24FSR4000JAP
1400R20VSJJAP
1400R20R180JAP
11R22.5R187JAP
12R22.5R187JAP
13R22.5R187JAP
275/70R22.5R294JAP
315/80R22R184JAP
315/80R22R184THI
315/80R22R152JAP
315/80R22G580JAP
Dayton 315/80R22DS50THI
425/65R22.5R164INDO
425/65R22.5R164JAP
385/65R22.5R164THI
385/65R22.5R164JAP
Dayton 385/65R22.5DT40THI
445/65R22.5R164JAP
325/95R24G582JAP
325/95R24M840JAP


sheet2
CategorySizePatternOrigin
PSR (LRD)175/70R13B25INDO
175/70R13EP150THI
185/70R13EP150INDO
175/65R14EP150INDO
175/70R14EP150THI
175/70R14MY02THI
185/65R14TECTHI
185/65R14EP150INDO
185/65R15TC10INDO
185/65R15T005INDO
185/65R15T01JAP
185/65R15B250JAP
195/60R15AR20INDO
195/60R15EP150THI
195/60R15T001JAP
195/65R15MY02THI
195/65R15EP150THI
195/65R15EP150JAP
195/55R16EP300THI
205/55R16RE003THI
205/65R15EP150INDO
205/65R15MY02THI
205/65R15T01JAP
FS 205/65R15TZ700JAP
215/65R15T005INDO
205/70R15694JAP
225/75R15CT697INDO
235/95R15CD618JAP
255/70R15CD840THI
205R16CD840THI
205/R16CD697THI
205/55R16AR20INDO
205/55R16G3JAP
205/55R16T001
205/55R16ER300POL
205/60R16AR20INDO
205/60R16T005THI
205/60R16T001JAP
205/65R16EP300INDO
215/55R16EP300INDO
215/60R16EP300THI
215/60R16EP200THI
215/60R16T005THI
215/60R16AR20INDO
215/65R16AR20INDO
225/55R16EP300INDO
225/55R16T001JAP
225/60R16AR20INDO
225/60R16EP300JAP
225/60R16T001JAP
225/70R16D687JAP
225/70R16H005THI
225/75R16T697INDO
235/60R16T01JAP
235/60R16ALENZAJAP
245/70R16D684THI
255/70R16T697INDO
255/70R16H005THI
265/70R16D850THI
265/70R16D840THI
275/70R16H005THI
275/70R16D694JAP
LT285/75R16AT001JAP
TTL
PSR (HRD)215/45R17T001JAP
215/50R17EP300THI
215/55R17GR90INDO
215/55R17T001JAP
215/55R17T005JAP
215/55R17TECTHI
215/55R17MY02THI
FS 215/55R17TZ700JAP
225/45R17RE050AJAP
225/45R17RE003JAP
FS 225/45R17TZ700JAP
FS 225/55R17TZ700JAP
225/55R17EP300THI
225/55R17D694JAP
225/60R17D-SPORTJAP
225/65R17T697INDO
225/70R17D697THI
235/55R17ER30JAP
235/55R17T005THI
235/55R17D400JAP
235/60R17D400JAP
235/65R17T697INDO
245/45R17T05AJAP
245/65R17D698JAP
245/65R17D697INDO
245/70R17D684JAP
245/75R17693AJAP
265/65R17D840JAP
285/65R17R683JAP
225/35R19RE050JAP
225/40R18GR90JAP
225/40R18GR90INDO
225/45R18AR20INDO
235/50R18ALENZAJAP
235/55R18E031JAP
235/60R18D-SPORTJAP
245/40R18RE050AJAP
245/40R19T005JAP
245/40ZR20S001JAP
245/45R18T001JAP
245/45R19T005JAP
255/35R18GR90JAP
255/40R19S001JAP
255/45R20DSPORTJAP
265/60R18D840JAP
265/70R18D684JAP
275/35R18RE050AJAP
275/40R18RE050AJAP
275/40R18S007JAP
275/40R19S007JAP
275/40R20DSPORTJAP
275/45R19ALENZAJAP
275/45R20DSPORTJAP
275/55R17DSPORTJAP
275/55R20ALENZAJAP
285/50R20DSPORT
275/65R18116HTHI
285/60R18T01JAP
285/60R18D850JAP
245/40R20RE050JAP
265/50R20SPORTJAP
275/30R20RE050JAP
275/40R20DHPJAP
275/55R20D680JAP
285/30R20RE0050AJAP
315/35R20SPORTJAP
TTL
LTR650R16R230
700R16R23012PR
750R16R230
155R12CR624INDO
155R12CR623INDO
165R13CR624INDO
185R14C613VJAP
185R14CR624INDO
195R14CR624INDO
195R14C613VJAP
195R14CR623THI
205R14C613VJAP
205R14CR624INDO
215R14CR624INDO
195R15C613VJAP
195R15CR623THI
195/70R15CR623JAP
195/75R16C613VJAP
205/70R15CR624INDO
205/70R15CR623THI
205/70R15CR623JAP
215/65R16CR624INDO
215/65R16CR611THI
215/70R15CR624INDO
215/70R15CR623THI
225/70R15CR623JAP
TTL
LSR750R16VSJ8PR
825R16R180JAP
TTL
TBR1200R20G580JAP
1200R20R187JAP
1200R20G580THI
1200R20R187THI
1200R20M802THI
Dayton 1200R24DA53THI
FS1200R20FSR4000JAP
1200R24G580JAP
1200R24G582JAP
FS1200R24FSR4000JAP
1400R20VSJJAP
1400R20R180JAP
11R22.5R187JAP
12R22.5R187JAP
13R22.5R187JAP
275/70R22.5R294JAP
315/80R22R184JAP
315/80R22R184THI
315/80R22R152JAP
315/80R22G580JAP
Dayton 315/80R22DS50THI
425/65R22.5R164INDO
425/65R22.5R164JAP
385/65R22.5R164THI
385/65R22.5R164JAP
Dayton 385/65R22.5DT40THI
445/65R22.5R164JAP
325/95R24G582JAP
325/95R24M840JAP
TTL


result in sheet1
catogerySizePatternOrigin
PSR (LRD)195/60R15EP150THI
195/60R15T001JAP
195/65R15MY02THI
195/65R15EP150THI
195/65R15EP150JAP
195/55R16EP300THI
205/55R16RE003THI
205/55R16RE003THI
205/65R15EP150INDO
205/65R15MY02THI
205/65R15T01JAP
FS 205/65R15TZ700JAP
215/65R15T005INDO
205/70R15694JAP
225/75R15CT697INDO
235/95R15CD618JAP
255/70R15CD840THI
205R16CD840THI
205/R16CD697THI
205/55R16AR20INDO
205/55R16G3JAP
205/55R16T001
205/55R16ER300POL
205/60R16AR20INDO
205/60R16T005THI
205/60R16T001JAP
205/65R16EP300INDO
215/55R16EP300INDO
215/60R16EP300THI
215/60R16EP200THI
215/60R16T005THI
215/60R16AR20INDO
215/65R16AR20INDO
225/55R16EP300INDO
225/55R16T001JAP
225/60R16AR20INDO
225/60R16EP300JAP
225/60R16T001JAP
225/70R16D687JAP
225/70R16H005THI
225/75R16T697INDO
235/60R16T01JAP
235/60R16ALENZAJAP
245/70R16D684THI
255/70R16T697INDO
255/70R16H005THI
265/70R16D850THI
265/70R16D840THI
275/70R16H005THI
275/70R16D694JAP
LT285/75R16AT001JAP
PSR (HRD)215/45R17T001JAP
215/50R17EP300THI
215/55R17GR90INDO
215/55R17T001JAP
215/55R17T005JAP
215/55R17TECTHI
215/55R17MY02THI
FS 215/55R17TZ700JAP
225/45R17RE050AJAP
225/45R17RE003JAP
FS 225/45R17TZ700JAP
FS 225/55R17TZ700JAP
225/55R17EP300THI
225/55R17D694JAP
225/60R17D-SPORTJAP
225/65R17T697INDO
225/70R17D697THI
235/55R17ER30JAP
235/55R17T005THI
235/55R17D400JAP
235/60R17D400JAP
235/65R17T697INDO
245/45R17T05AJAP
245/65R17D698JAP
245/65R17D697INDO
245/70R17D684JAP
245/75R17693AJAP
265/65R17D840JAP
285/65R17R683JAP
225/35R19RE050JAP
225/40R18GR90JAP
225/40R18GR90INDO
225/45R18AR20INDO
235/50R18ALENZAJAP
235/55R18E031JAP
235/60R18D-SPORTJAP
245/40R18RE050AJAP
245/40R19T005JAP
245/40ZR20S001JAP
245/45R18T001JAP
245/45R19T005JAP
255/35R18GR90JAP
255/40R19S001JAP
255/45R20DSPORTJAP
265/60R18D840JAP
265/70R18D684JAP
275/35R18RE050AJAP
275/40R18RE050AJAP
275/40R18S007JAP
275/40R19S007JAP
275/40R20DSPORTJAP
275/45R19ALENZAJAP
275/45R20DSPORTJAP
275/55R17DSPORTJAP
275/55R20ALENZAJAP
285/50R20DSPORT
275/65R18116HTHI
285/60R18T01JAP
285/60R18D850JAP
245/40R20RE050JAP
265/50R20SPORTJAP
275/30R20RE050JAP
275/40R20DHPJAP
275/55R20D680JAP
285/30R20RE0050AJAP
315/35R20SPORTJAP
LTR650R16R230
700R16R23012PR
750R16R230
155R12CR624INDO
155R12CR623INDO
165R13CR624INDO
185R14C613VJAP
185R14CR624INDO
195R14CR624INDO
195R14C613VJAP
195R14CR623THI
205R14C613VJAP
205R14CR624INDO
215R14CR624INDO
195R15C613VJAP
195R15CR623THI
195/70R15CR623JAP
195/75R16C613VJAP
205/70R15CR624INDO
205/70R15CR623THI
205/70R15CR623JAP
215/65R16CR624INDO
215/65R16CR611THI
215/70R15CR624INDO
215/70R15CR623THI
225/70R15CR623JAP
LSR750R16VSJ8PR
825R16R180JAP
TBR1200R20G580JAP
1200R20R187JAP
1200R20G580THI
1200R20R187THI
1200R20M802THI
Dayton 1200R24DA53THI
FS1200R20FSR4000JAP
1200R24G580JAP
1200R24G582JAP
FS1200R24FSR4000JAP
1400R20VSJJAP
1400R20R180JAP
11R22.5R187JAP
12R22.5R187JAP
13R22.5R187JAP
275/70R22.5R294JAP
315/80R22R184JAP
315/80R22R184THI
315/80R22R152JAP
315/80R22G580JAP
Dayton 315/80R22DS50THI
425/65R22.5R164INDO
425/65R22.5R164JAP
385/65R22.5R164THI
385/65R22.5R164JAP
Dayton 385/65R22.5DT40THI
445/65R22.5R164JAP
325/95R24G582JAP
325/95R24M840JAP
the stars shows I no know why happens , but this short word JAPAN contain the first letters
thanks in advance
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
366
Alex Blakenburg
A
A
  • Solved
autofill column for each item based on matching column between two sheets
Replies
7
Views
373
Legacy 143009
L
A
  • Solved
replace item for range with item for Table based on column
2
Replies
13
Views
691
Legacy 143009
L
A
  • Question
Highlight and delete duplicates items in the different spaces for multiple columns
Replies
1
Views
330
abdo meghari
A
A
  • Solved
replace column with another based on match column between two sheets
Replies
7
Views
404
JoeMo
J

Forum statistics

Threads
1,216,101
Messages
6,128,844
Members
449,471
Latest member
lachbee

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