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

    Delete Duplicates based on another field (2000)

    First, I have a spreadsheet with a field called "Group Number".
    I have a field called "Group Market Status" which could be a 2 (cancelled) or 100 (Active). I want to delete duplicate group numbers based on the Group Market Status field. If there are two group numbers 0001, I want to delete the one that has a group market status of 2.
    Is there a macro that could do this?

    Thanks for your help....

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Duplicates based on another field (2000)

    We could write a quick macro for you, but would need a little additional information:

    1- What do you want to do if there are two group numbers and neither of them have a group market status of 2 (ie. they both have a group market status of 100)?

    2- What columns are the Group Number and Group Market Status in? If I have to find them, what row are the header labels in?

    3- What do you want to delete? The whole row?

    4- Is the worksheet sorted by Group Number and Group Market Index? If it is not sorted, can the macro sort it or do you want it to remain in the current order?

    It would be a big help to have a copy of the workbook to use for testing. Could you upload the workbook with any proprietary info altered?
    Legare Coleman

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

    Re: Delete Duplicates based on another field (2000)

    1. There was supposed to be only one group number but we found a fluke in the data where there are two of the same group numbers - the only difference between them is that one is cancelled and one is active according to that group Market status field.
    2. Column B has group number and Column K has Group Market Status. Header labels in first row.
    3. Yes - delete the whole row
    4. Remain in current order - it is not sorted.
    5. If you can do the macro with the info I have supplied, that would be fine. All of the data is sensitive.

    Thanks for your help....

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Duplicates based on another field (2000)

    This worked on my test sheet:

    <pre>Public Sub DelDups()
    Dim I As Long, J As Long
    With ActiveSheet
    For I = .Range("B65536").End(xlUp).Row - 1 To 1 Step -1
    If .Range("G1").Offset(I, 0).Value = 2 Then
    For J = .Range("B65536").End(xlUp).Row - 1 To 1 Step -1
    If I <> J And .Range("B1").Offset(I, 0).Value = .Range("B1").Offset(J, 0).Value Then
    .Range("B1").Offset(I, 0).EntireRow.Delete
    Exit For
    End If
    Next J
    End If
    Next I
    End With
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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