Results 1 to 5 of 5

Thread: Need a Macro

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need a Macro

    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.
    Last edited by LindaR; 2013-10-07 at 10:58.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
    Last edited by sdckapr; 2013-10-07 at 11:38. Reason: add the repeating element for additonal rows

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Steve. That worked. Thanks for your help.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    A VBA approach.

    -----------before code-------------------after code-------
    dups1.png dup3.png

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •