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

A

abdo meghari

Active Member
Joined
Aug 3, 2021
Messages
465
Office Version
  1. 2019
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
ABCDEF
1CODEBRANDCODEBRAND
2BSJ100BS 1200R20 G580 JAPBSJ103BS 1200R20 R187 JAP
3BSJ100BS 1200R20 G580 JAPBSJ104BS 1200R20 R187 THI
4BSJ100BS 1200R20 G580 JAPBSJ105BS 1200R24 22PR G580
5BSJ100BS 1200R20 G580 JAPBS 1200R20 G580 TCF
6BSJ100BS 1200R20 G580 JAPBS 1200R24 G582 JAP
7BSJ101BS 1200R20 G580 THIBSJ107BS 1400R20 R180 JAP
8BSJ101BS 1200R20 G580 THIBS 1400R20 TCF 20PR R180BZ JAP
9BSJ101BS 1200R20 G580 THIBSJ109BS 1400R20 VSJ TCF JAP
10BSJ102BS 1200R24 G582 JAPBSJ110BS 155R12C R624 IND
11BSJ102BS 1200R24 G582 JAP
12BSJ102BS 1200R24 G582 JAP
13BSJ102BS 1200R24 G582 JAP
14BSJ103BS 1200R20 R187 JAP
15BSJ103BS 1200R20 R187 JAP
16BSJ104BS 1200R20 R187 THI
17BSJ105BS 1200R24 G580 JAP
18BSJ105BS 1200R24 G580 JAP
19BSJ106BS 13R22.5 R187 JAP
20BSJ107BS 1400R20 R180 JAP
21BSJ107BS 1400R20 R180 JAP
22BSJ108BS 1400R20 R180BZ JAP
23BSJ109BS 1400R20 VSJ JAP
24BSJ109BS 1400R20 VSJ JAP
25BSJ109BS 1400R20 VSJ JAP
26BSJ110BS 155R12C R624 INDO
27BSJ111BS 155R12C R623 INDO
MAIN


result
FILL.xlsm
ABCDEF
1CODEBRANDCODEBRAND
2BSJ103BS 1200R20 R187 JAPBSJ103BS 1200R20 R187 JAP
3BSJ104BS 1200R20 R187 THIBSJ104BS 1200R20 R187 THI
4BSJ105BS 1200R24 G580 JAPBSJ105BS 1200R24 22PR G580
5BS 1200R20 G580 TCFBS 1200R20 G580 TCF
6BS 1200R24 G582 JAPBS 1200R24 G582 JAP
7BSJ107BS 1400R20 R180 JAPBSJ107BS 1400R20 R180 JAP
8BS 1400R20 TCF 20PR R180BZ JAPBS 1400R20 TCF 20PR R180BZ JAP
9BSJ109BS 1400R20 VSJ TCF JAPBSJ109BS 1400R20 VSJ TCF JAP
10BSJ110BS 155R12C R624 INDBSJ110BS 155R12C R624 IND
RESULT

thanks in advance
 
Last edited:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
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?
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
OMG!!!:eek::eek::eek:
sorry 🙏🙏
here is again
FILL.xlsm
ABCDEF
1CODEBRANDCODEBRAND
2BSJ100BS 1200R20 G580 JAPBSJ103BS 1200R20 R187 TCF
3BSJ100BS 1200R20 G580 JAPBSJ104BS 1200R20-18PR R187 THI
4BSJ100BS 1200R20 G580 JAPBSJ105BS 1200R24 G580
5BSJ100BS 1200R20 G580 JAPBS 1200R20 G580 TCF
6BSJ100BS 1200R20 G580 JAPBS 1200R24 G582 JAP
7BSJ101BS 1200R20 G580 THIBSJ107BS 1400R20 TCF R180
8BSJ101BS 1200R20 G580 THIBS 1400R20 TCF 20PR R180BZ JAP
9BSJ101BS 1200R20 G580 THIBSJ109BS 1400R20 VSJ TCF
10BSJ102BS 1200R24 G582 JAPBSJ110BS 155 R12C R624 IND
11BSJ102BS 1200R24 G582 JAP
12BSJ102BS 1200R24 G582 JAP
13BSJ102BS 1200R24 G582 JAP
14BSJ103BS 1200R20 R187 JAP
15BSJ103BS 1200R20 R187 JAP
16BSJ104BS 1200R20 R187 THI
17BSJ105BS 1200R24 G580 JAP
18BSJ105BS 1200R24 G580 JAP
19BSJ106BS 13R22.5 R187 JAP
20BSJ107BS 1400R20 R180 JAP
21BSJ107BS 1400R20 R180 JAP
22BSJ108BS 1400R20 R180BZ JAP
23BSJ109BS 1400R20 VSJ JAP
24BSJ109BS 1400R20 VSJ JAP
25BSJ109BS 1400R20 VSJ JAP
26BSJ110BS 155R12C R624 INDO
27BSJ111BS 155R12C R623 INDO
MAIN


result
FILL.xlsm
ABCDEF
1CODEBRANDCODEBRAND
2BSJ103BS 1200R20 R187 JAPBSJ103BS 1200R20 R187 TCF
3BSJ104BS 1200R20 R187 THIBSJ104BS 1200R20-18PR R187 THI
4BSJ105BS 1200R24 G580 JAPBSJ105BS 1200R24 G580
5BS 1200R20 G580 TCFBS 1200R20 G580 TCF
6BS 1200R24 G582 JAPBS 1200R24 G582 JAP
7BSJ107BS 1400R20 R180 JAPBSJ107BS 1400R20 TCF R180
8BS 1400R20 TCF 20PR R180BZ JAPBS 1400R20 TCF 20PR R180BZ JAP
9BSJ109BS 1400R20 VSJ TCF JAPBSJ109BS 1400R20 VSJ TCF
10BSJ110BS 155R12C R624 INDOBSJ110BS 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]
 
Upvote 0
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?
 
Upvote 0
I don't know what is different between two tables but I think it doesn't explain my question.
Click to expand...
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?
Click to expand...
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 .
 
Upvote 0
I think this should work:
VBA Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
replace column with another based on match column between two sheets
Replies
7
Views
387
JoeMo
J
A
  • Solved
autofill column for each item based on matching column between two sheets
Replies
7
Views
348
Legacy 143009
L
A
  • Solved
using dropdown(combobox) instead of data validation to autocomplete items
2
Replies
15
Views
1K
Akuini
Akuini
A
  • Solved
auto fill for two lists under last duplicates items based on matching column
2
Replies
14
Views
605
Legacy 143009
L
A
  • Solved
replace item for range with item for Table based on column
2
Replies
13
Views
641
Legacy 143009
L

Forum statistics

Threads
1,215,079
Messages
6,123,005
Members
449,092
Latest member
masterms

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