Remove Duplicates In Column B For Each Value In Column A? (VBA Or Formula)

[DerekK]

Good day,

I need to remove the duplicates under column B for each item under column A and I can't seem to figure out how to do it.

I'm using Excel 2007; I would prefer a VBA solution, as I have to do this on a monthly basis. However, if a formula is the best way to go, then that's just fine. Either way, I'm preparing the data for a pivot table, if that helps. I am unable to post the actual document in the interest of data protection.

Essentially, I'd like to turn this-

_ A B
1 Paris Bill
2 Paris Bill
3 Paris Mike
4 Paris Derek
5 Paris Derek
6 London Mike
7 London Bill
8 London Mike
9 London Bill
10 London Derek
11 Tokyo Derek
12 Tokyo Derek
13 Tokyo Derek


into this-

_ A B
1 Paris Bill
2 Paris Mike
3 Paris Derek
4 London Mike
5 London Bill
6 London Derek
7 Tokyo Derek

In other words, each city should have only one instance of any name that accompanies it; not all names will accompany each city.

Hope this make sense and I hope you can help!

Sincerely,



Derek

 

[bewsh1987]

if this is something that you are doing vary rare than you can do this manually

select the columnon the ribbon

tab data

then you will see remove duplicates

this should work better for you

With ActiveSheet.Range("A:A")
.RemoveDuplicates Columns:=1, Header:=xlNo
End With

 

[DerekK]

Thank you for your reply.

Just to clarify, I don't want to remove all duplicates under column B. Using the example in my original post, I just want to remove duplicates for each city; so, for example, Bill appears twice for Paris and twice for London, whereas I would like Bill to appear once for Paris and once for London.

I hope this make sense; I apologize if I've misunderstood your solution.

 

[bewsh1987]

No Problem

When you manually select remove duplicate you need to untick the columns you don't want

the below code should do this

you can change the range as you desire

With ActiveSheet.Range("A:B")
.RemoveDuplicates Columns:=1, Header:=xlNo
End With

Old
Bill	Paris
Bill	Paris
Bill	Paris
Jack	Spain
Jack	Spain
Jack	Spain
Jack	Spain
Jack	Spain
New
Bill	Paris
Jack	Spain

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

 

[DerekK]

Sorry to belabor this but I tried that and it still doesn't work...

To use bewsh1987's example, it should look like this-

Old
Bill Paris
Bill Paris
Bill Paris
Bill Spain
Bill Spain
Bill Greece
Jack Paris
Jack Spain
Jack Spain
Jack Spain

New
Bill Paris
Bill Spain
Bill Greece
Jack Paris
Jack Spain

 

[bewsh1987]

Sorry I missed a little code

please try tested and works

With ActiveSheet.Range("A:B")
    .RemoveDuplicates Columns:=Array(1, 2), Header _
        :=xlNo
        
End With

Manual Way</SPAN>
1</SPAN>	Highlight the cell A:B</SPAN>
2</SPAN>	Select the data tab on the ribbon</SPAN>
3</SPAN>	On the 4th section there is Remove Duplicates</SPAN>
4</SPAN>	A box will appear with Column A & Column B Ticked</SPAN>
5</SPAN>	Click ok for Result</SPAN>

<TBODY>
</TBODY>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG08Jan53
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value & Dn.Offset(, 1)) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value & Dn.Offset(, 1), Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[DerekK]

I tried bewsh1987's version, it worked just fine.

Many thanks to both of you!

 

[MickG]

Bewsh, That Array bit is an interesting and useful addition to the "Remove Duplicates" function.
Something I was not aware of.
Regrds Mick