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]

Awesome !
could you make result in RESULT sheet, please?

 

[fjns]

Hi, see the linked file for a possible solution (with 10 auxiliary columns, without VBA)...

The formulas used in the table...
MAIN!I2: =IF(F2="","",IF(E2="",IF(ROW()=2,"",I1),E2))
MAIN!J2: =IF(F2="","",COUNTIF(I$2:I2,I2))
MAIN!K2: =IF(F2="","",IF(J2>IFERROR(VLOOKUP(I2,O:P,2,0),0),IF(J2>IFERROR(VLOOKUP(I2,O:Q,3,0),0),"F","n"),"n"))
MAIN!M2: =IF(B2="","",IF(AND(A2=A1,B2=B1),0,1))
MAIN!N2: =IF(B2="","",SUM(M$2:M2*(A$2:A2=A2)))
MAIN!O2: =IF(B2="","",A2)
MAIN!P2: =IF(B2="","",SUMIF(A:A,A2,M:M))
MAIN!Q2: =IF(B2="","",COUNTIF(A:A,A2))
MAIN!R2: =IF(B2="","",A2&"#"&N2)
MAIN!S2: =IF(B2="","",B2)
RESULT!A2: =IF(MAIN!F2="","",IF(MAIN!E2="","",MAIN!E2))
RESULT!B2: =IF(MAIN!F2="","",IF(MAIN!J2>IFERROR(VLOOKUP(MAIN!I2,MAIN!O:P,2,0),0),MAIN!F2,VLOOKUP(MAIN!I2&"#"&MAIN!J2,MAIN!R:S,2,0)))
RESULT!E2: =IF(MAIN!F2="","",IF(MAIN!E2="","",MAIN!E2))
RESULT!F2: =IF(MAIN!F2="","",MAIN!F2)

The formulas for Conditional Formatting...
to MAIN!F:F =K1="F"
to RESULT!B:B =MAIN!K1="F"
to RESULT!F:F =MAIN!K1="F"

FILL.xlsx

 

[Legacy 143009]

Awesome !
could you make result in RESULT sheet, please?

Does RESULT sheet exist or do you want the code create a new sheet?

 

[abdo meghari]

@fjns
thanks and I appreciate for your effort , but I prefer vba, sorry!

 

[abdo meghari]

Does RESULT sheet exist or do you want the code create a new sheet?

Well , I choose creating new sheet .

 

[Legacy 143009]

This should work:

Sub test()
  Dim RESULT As Worksheet
  Dim RESULTRange1 As Range, RESULTRange2 As Range, r As Range, c As Integer
 
  Worksheets("MAIN").Copy
  Set RESULT = ActiveSheet
  RESULT.Name = "RESULT"
  Set RESULTRange1 = Intersect(RESULT.UsedRange, RESULT.Range("A:B"))
  Set RESULTRange2 = Intersect(RESULT.UsedRange, RESULT.Range("E:F"))
 
  With RESULTRange1
  For i = .Rows.Count To 2 Step -1
      Set r = RESULTRange2.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
  RESULTRange1.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
  With RESULTRange2
  For i = 2 To .Rows.Count
    If .Cells(i, 1).Value = "" Then
      RESULTRange1.Cells(Application.Match(.Cells(i, 1).End(xlUp).Value, RESULTRange1.Columns(1), 0), 1).Offset(1 + c).Resize(, 2).Insert Shift:=xlDown
      RESULTRange1.Cells(Application.Match(.Cells(i, 1).End(xlUp).Value, RESULTRange1.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

 

[abdo meghari]

this deletes MAIN sheet , should keep it !

 

[Legacy 143009]

Noo.. Howcome?? It should just copy :/ Strange. Let me test

 

[abdo meghari]

umm, you add new file !
just new sheet in the same file .

 

[Legacy 143009]

No.. Worksheets("MAIN").Copy should just copy the sheet. No new file