Comparing two lists and removing duplicates

GLPedley

New Member
Hello

I've attached a file with two lists, how can I compare these two lists and remove any duplicates from Column B that appear in Column A

Thanks
G

Attachments

• 8.8 KB Views: 8

Marc L

Excel Ninja
Hi !​
Formula in cell C2 : `=ISNUMBER(MATCH(B2,\$A\$2:\$A\$13,0))` then copy down until the last column B fruit …​
Select columns B & C then apply an ascending sort on column C then you can delete at once all the fruits in B with TRUE in C​
then clear column C, that's it !​

Peter Bartholomew

Well-Known Member
Please excuse this answer but I am interested in seeing how the new dynamic arrays might change the way in which Excel formulas may be written. It is only of any value to those who have or are likely to lease Office 365. The list of additional distinct fruit from the second column may be given by
= FILTER( Table1[Product2], ISERROR( XMATCH( Table1[Product2], Table1[Product1] ) ) )

Peter Bartholomew

Well-Known Member
This is just shameless playing, but I am learning! To append the two columns, I defined n to be the size of the Table1
= ROWS(Table1)

and k, referring to
= SEQUENCE( 2*n )
to be an index running over the double length array. The appended array
= IF( k<=n, Table1[Product1], INDEX( Table1[Product2], k-n ) )
is calculated in memory and a sorted list of distinct fruits taken from the two columns is returned by the dynamic array formula
= SORT( UNIQUE( appended ) )

p.s.
Microsoft guidance is not to nest named formulas and (maybe) one day I will do what I am told -- but not yet .

deciog

Active Member
Peter

I am also learning OFFICE 385, I would like you to attach a spreadsheet with the examples of the formulas, because I can not assemble them always gives errors, with examples spreadsheets the translation is automatic and helps in my training

I like the way you're doing

Decio

Peter Bartholomew

Well-Known Member
Decio
Oops! I deleted the file.
Anyway, thank you for the interest; I sometimes fear that I create more confusion than enlightenment with my posts .
The columns to the right are duplicate calculations that are intended to show the way in which the solutions were built but, since they have no part in the actual calculation, everything from column H onward could be deleted.
Cheers
Peter

Attachments

• 19.8 KB Views: 3

AlanSidman

Well-Known Member
Here is another option. This is a VBA solution.
Code:
``````Option Explicit

Sub Dupes()
Dim i As Long, j As Long
Dim lrA As Long, lrB As Long
lrA = Range("A" & Rows.Count).End(xlUp).Row
lrB = Range("B" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = lrB To 2 Step -1
For j = 2 To lrA
If Range("B" & i) = Range("A" & j) Then
Range("B" & i).Delete
End If
Next j
Next i
Application.ScreenUpdating = True
MsgBox "completed"
End Sub``````

Marc L

Excel Ninja
If really a VBA code is necessary it's better to follow the post #2 way as it not needs any loop and​
the more non contiguous rows to delete this way is faster than deleting row by row inside a loop …​

deciog

Active Member
Peter, Good Morning, here in Brazil is now 7:30 am

Thank you was great, your posting is very good, and I'm learning and I'm sure others are also learning.

Hugs
Decio