• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comparing two lists and removing duplicates


New Member

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



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 ) )

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


Active Member

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


Peter Bartholomew

Well-Known Member
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.



Active Member
Here is another option. This is a VBA solution.
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 …​


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.