clear cells for duplicates items for list and sort by adjacent cells for another list

[abdo meghari]

Hello
I want matching column CODE between two lists (A,E) should clear all of items are existed in A:B but not existed in E: F and sort list A: B based on list E:F ,and if items are existed in A:B and existed in E: F when sort list A: B based on list E:F should delete duplicates items from list A:B when sorting
and when sort list A:B should copy from list E:F contains empty cells for column E to list A:B . so should put two lists for adjacent cells each other of them are matched ( the same code) based on code (A,E) when sorting.


original

FILL.xlsm
	A	B	C	D	E	F
1	CODE	BRAND			CODE	BRAND
2	BSJ100	BS 1200R20 G580 JAP			BSJ103	BS 1200R20 R187 JAP
3	BSJ100	BS 1200R20 G580 JAP			BSJ104	BS 1200R20 R187 THI
4	BSJ100	BS 1200R20 G580 JAP			BSJ105	BS 1200R24 22PR G580
5	BSJ100	BS 1200R20 G580 JAP				BS 1200R20 G580 TCF
6	BSJ100	BS 1200R20 G580 JAP				BS 1200R24 G582 JAP
7	BSJ101	BS 1200R20 G580 THI			BSJ107	BS 1400R20 R180 JAP
8	BSJ101	BS 1200R20 G580 THI				BS 1400R20 TCF 20PR R180BZ JAP
9	BSJ101	BS 1200R20 G580 THI			BSJ109	BS 1400R20 VSJ TCF JAP
10	BSJ102	BS 1200R24 G582 JAP			BSJ110	BS 155R12C R624 IND
11	BSJ102	BS 1200R24 G582 JAP
12	BSJ102	BS 1200R24 G582 JAP
13	BSJ102	BS 1200R24 G582 JAP
14	BSJ103	BS 1200R20 R187 JAP
15	BSJ103	BS 1200R20 R187 JAP
16	BSJ104	BS 1200R20 R187 THI
17	BSJ105	BS 1200R24 G580 JAP
18	BSJ105	BS 1200R24 G580 JAP
19	BSJ106	BS 13R22.5 R187 JAP
20	BSJ107	BS 1400R20 R180 JAP
21	BSJ107	BS 1400R20 R180 JAP
22	BSJ108	BS 1400R20 R180BZ JAP
23	BSJ109	BS 1400R20 VSJ JAP
24	BSJ109	BS 1400R20 VSJ JAP
25	BSJ109	BS 1400R20 VSJ JAP
26	BSJ110	BS 155R12C R624 INDO
27	BSJ111	BS 155R12C R623 INDO
MAIN

result

FILL.xlsm
	A	B	C	D	E	F
1	CODE	BRAND			CODE	BRAND
2	BSJ103	BS 1200R20 R187 JAP			BSJ103	BS 1200R20 R187 JAP
3	BSJ104	BS 1200R20 R187 THI			BSJ104	BS 1200R20 R187 THI
4	BSJ105	BS 1200R24 G580 JAP			BSJ105	BS 1200R24 22PR G580
5		BS 1200R20 G580 TCF				BS 1200R20 G580 TCF
6		BS 1200R24 G582 JAP				BS 1200R24 G582 JAP
7	BSJ107	BS 1400R20 R180 JAP			BSJ107	BS 1400R20 R180 JAP
8		BS 1400R20 TCF 20PR R180BZ JAP				BS 1400R20 TCF 20PR R180BZ JAP
9	BSJ109	BS 1400R20 VSJ TCF JAP			BSJ109	BS 1400R20 VSJ TCF JAP
10	BSJ110	BS 155R12C R624 IND			BSJ110	BS 155R12C R624 IND
RESULT

thanks in advance

 

[abdo meghari]

No new file

but this is what happens !

 

[Legacy 143009]

OK modify this line:

Worksheets("MAIN").Copy

like this:

Worksheets("MAIN").Copy After:=Worksheets("MAIN")

Yes I think you are right. It copies the sheet to a new WB if you don't explicitly indicate before or after which sheet.

 

[abdo meghari]

well done !
finally works excellently .
much appreciated for your answering

 

[Legacy 143009]

No problem. I like this project