Delete duplicate values in a cell

Question

I want to remove duplicate values in each cell in column B. Each value in the cell is separated by a line feed.

Cells B5, B6 and B7 has duplicates while B4 has unique. Duplicates removed are shown in the cell B14, B15 and B16.

Then I want to filter those cells that has more that one value in column B.

Answer 1

Here is a macro you can run:

Sub RemoveDups()
    Dim r As Long
    Dim m As Long
    Dim a() As String
    Dim d As Object
    Dim v As Variant
    Application.ScreenUpdating = False
    m = Range("B" & Rows.Count).End(xlUp).Row
    For r = 4 To m
        a = Split(Range("B" & r).Value, vbLf)
        Set d = CreateObject(Class:="Scripting.Dictionary")
        For Each v In a
            d(v) = Null
        Next v
        Range("B" & r).Value = Join(d.Keys, vbLf)
    Next r
    Application.ScreenUpdating = True
End Sub

Answer 2

Yes, indeed (actually d.Keys).

Answer 3

This is good, so a dictionary was created with out duplicates by simply reassigning Null value to it.

For each item, key:value pair, key is the unique number value is Null (like a place holder).

All these key:value pairs are stored in the variable d. Therefore, Join(d.key)

Thank you very much for the explanation.

Answer 4

The Dictionary object of part of the Scripting Runtime library. A dictionary may contain all kinds of items. Each item must have a unique key (think of the key as the "name" of the item).

The line d(v) = Null does one of two things:

• If an item with key v (which is one of the lines in the cell) doesn't exist yet, it creates an item with v as key, and Null as value (we aren't interested in the value, we only need the keys).
• If an item with key v already exists (i.e. if it is a duplicate line), the line merely sets the value of that item to Null again. So we don't create duplicate items.

At the end of the loop, the dictionary contains an item for each unique line. We then join the keys with a line feed as delimiter.

See for example: Excel VBA Dictionary – A Complete Guide

Answer 5

It worked as expected.

I have few question about the code. I am trying to visualize the code I have marked with comment.

I did not knew about dictionary object in VBA.

m = Range("B" & Rows.Count).End(xlUp).Row  'last row

    For r = 4 To m   'B4 to B7

        a = Split(Range("B" & r).Value, vbLf)  'array ("20020335t" "21015166t") 

        Set d = CreateObject(Class:="Scripting.Dictionary") 

        For Each v In a   '"20020335t"

            d(v) = Null     'd("20020335t") = Null

        Next v 

        Range("B" & r).Value = Join(d.Keys, vbLf)  'Join unique keys by line feed

Next r

I did not understand how duplicates are eliminated through null.

d(v) = Null

I see create object used for outlook and power point can we also create class objects? How dictionary work in VBA?

The above code has no keys:value pairs how keys are identified.

Join(d.Keys, vbLf)