Need a Macro

Topic

New Reply

In column A, I have Client Number. I sorted on column A and removed the duplicate client numbers where there are duplicates. In Column N, I have a Contact name. For every break in Column A, client number, If there are different contact names, I want to delete all but the first contact name. If they are the same that is ok and not to be removed.

A N
001 Joe Smith
Bill Gates
Judy Jones
Remove Bill Gates and Judy Jones

A
002 Joe Smith
Joe Smith
Leave it

I only want to remove the rows when there are more than one client number and different names in column N. There could be 3 different names in column N for a client number.

Is this possible? I am trying to do this manually but it is taking forever.

Reply | Quote

Replies

I thought I had posted a response, but it seems to have disappeared (or I forgot to press the post buttone…)
You don’t really need a macro. In a blank column, you can create a comparison column in row 2:
=N2=n1
Then copy this down the column

Add autofilters to the dataset
Filter on the comparison column for FALSE and column A for Blank
This will display the blank ones in A that do not match the name in N
Delete these rows

Unfilter, recopy formula down the column and repeat…

remove the autofilter
delete the cmparison column

Steve

Reply | Quote

Ah, I had NOT forgotten to post it, I replied to the cross-posted message at http://eileenslounge.com/viewtopic.php?f=27&t=14312

Steve

Reply | Quote

Thank you Steve. That worked. Thanks for your help.

Reply | Quote

A VBA approach.

———–before code——————-after code——-
35088-dups1 35090-dup3

Code:

Public Sub deleteNames()
LastRow = ActiveSheet.Cells(Rows.Count, 14).End(xlUp).Row
For I = 1 To LastRow
    If Cells(I, 1)  “” Then
        Name = Cells(I, 14).Value
    Else
        If Cells(I, 14).Value  Name Then
            Cells(I, 14).Value = “”
        End If
    End If
Next I
End Sub

Reply | Quote