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

 

[Legacy 143009]

I want to take a different approach. In the end of the day, two lists will be identical if column F value exists in column B. Isn't it?

 

[abdo meghari]

no , you can't match between columns B,F . as you note there are different the items , how put to adjacent cells ?
that's why I create the CODE column for two lists .( will be the same code for different brand to treat this different between two lists)

 

[Legacy 143009]

I have to match columns B and F in some way..

For example, take BSJ106 on column A. How can I decide if I am going to delete complete row or just empty the Code number? I have to compare Brands at some point.

 

[abdo meghari]

OMG!!!
sorry
here is again

FILL.xlsm
	A	B	C	D	E	F
1	CODE	BRAND			CODE	BRAND
2	BSJ100	BS 1200R20 G580 JAP			BSJ103	BS 1200R20 R187 TCF
3	BSJ100	BS 1200R20 G580 JAP			BSJ104	BS 1200R20-18PR R187 THI
4	BSJ100	BS 1200R20 G580 JAP			BSJ105	BS 1200R24 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 TCF R180
8	BSJ101	BS 1200R20 G580 THI				BS 1400R20 TCF 20PR R180BZ JAP
9	BSJ101	BS 1200R20 G580 THI			BSJ109	BS 1400R20 VSJ TCF
10	BSJ102	BS 1200R24 G582 JAP			BSJ110	BS 155 R12C 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 TCF
3	BSJ104	BS 1200R20 R187 THI			BSJ104	BS 1200R20-18PR R187 THI
4	BSJ105	BS 1200R24 G580 JAP			BSJ105	BS 1200R24 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 TCF R180
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
10	BSJ110	BS 155R12C R624 INDO			BSJ110	BS 155 R12C R624 IND
RESULT

f:tim|fz:12pt|cls:ww]BSJ110[/XD][XD=h:c|v:t|ff:tim|fz:12pt|cls:ww]BS 155 R12C R624 IND[/XD][/XR][/RANGE]

 

[Legacy 143009]

I don't know what is the difference between two tables but I think it doesn't explain my question.
Let say I come to A19. What makes me to decide if just empty the cell or delete the entire row? What is the decision condition?

 

[abdo meghari]

I don't know what is different between two tables but I think it doesn't explain my question.

strange !
did you see the last picture just see B2,F2 as in example , is it same?

Let say I come to A19. What makes me to decide if just empty the cell or delete the entire row? What is the decision condition?

I don't understand A19 !!
first you need clear cells . I think you can't use deleting rows with this way .
so my idea , what is existed in column A and is not existed in column E , then should clear from column A:B and if what is existed in column A and is existed in column E ,then should clear duplicates from column A:B and sorting based on columns E: F and what is existed in column F and empty cell for column E then should copy to adjacent cell for column B .

 

[Legacy 143009]

Ok, I think I get it.
Is column A always in ascending order?

 

[abdo meghari]

Is column A always in ascending order?

yes like the column E when sorting .

 

[Legacy 143009]

I think this should work:

Sub test()
  Dim MAIN As Worksheet, table1 As Worksheet
  Dim table1Range As Range, MAINRange1 As Range, MAINRange2 As Range, r As Range, c As Integer
  
  Set MAIN = Worksheets("MAIN")
  Set MAINRange1 = Intersect(MAIN.UsedRange, MAIN.Range("A:B"))
  Set MAINRange2 = Intersect(MAIN.UsedRange, MAIN.Range("E:F"))
  
  With MAINRange1
  For i = .Rows.Count To 2 Step -1
      Set r = MAINRange2.Columns(1).Find(what:=.Cells(i, 1).Value, LookIn:=xlFormulas, lookat:=xlWhole)
      If r Is Nothing Then
        .Cells(i, 1).Resize(, 2).Delete Shift:=xlUp
      End If
  Next
  End With
  MAINRange1.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
  With MAINRange2
  For i = 2 To .Rows.Count
    If .Cells(i, 1).Value = "" Then
      MAINRange1.Cells(Application.Match(.Cells(i, 1).End(xlUp).Value, MAINRange1.Columns(1), 0), 1).Offset(1 + c).Resize(, 2).Insert Shift:=xlDown
      MAINRange1.Cells(Application.Match(.Cells(i, 1).End(xlUp).Value, MAINRange1.Columns(1), 0), 2).Offset(1 + c).Value = .Cells(i, 2).Value
      c = c + 1
    Else
      c = 0
    End If
  Next
  End With
End Sub