Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Separating duplicate records. (XP & 2003)

    Is there a way of separating out duplicate items (rows with duplicate names) from the rest of the spread sheet data?

    I attached a spread sheet where I highlighted some of the duplicates ( first names). I would want all duplicates to be separated so I can determine if I need the second record.

    Thanks

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

    Re: Separating duplicate records. (XP & 2003)

    Yes, you can do this with conditional formatting. Take a look at the attached.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Separating duplicate records. (XP & 2003)

    I'm sorry, I do not see where the duplicate entries are segregated from the non duplicate entries.

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

    Re: Separating duplicate records. (XP & 2003)

    They are all hilited in yellow, as they were in your file. Is that not what you wanted?
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Separating duplicate records. (XP & 2003)

    I highlighted them for illustration purposes however, I would like to physically locate the duplicates on another section of the worksheet or on a different worksheet.

    Sorry for the confusion.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Separating duplicate records. (XP & 2003)

    As an aside, How were you able to do the coditional formatting anyway? Pretty Cool.

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

    Re: Separating duplicate records. (XP & 2003)

    Select one of the cells and then select "Conditional Formatting" from the Format menu.
    Legare Coleman

  8. #8
    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

    Re: Separating duplicate records. (XP & 2003)

    In Cell K2 add the formula
    <pre>=COUNTIF($A$2:A2,A2)>1</pre>


    copy it from K2 to K3:K275

    It will display TRUE when the name is duplicated in range, false when it is unique or the first one listed.

    select K2 Data -filter - autofilter
    Select colum K pulldown and choose TRUE
    Select the first filtered data row and while holding shift, select the last filtered datarow
    Then edit -copy to copy these rows
    go to where you want to "move them" and select paste
    Then go back to the source sheet
    Edit - delete rows to eliminate the dupes from the list

    data -f ilter uncheck autofilter.

    This sheet has all the unique names, the other sheets any duplicates.

    If you have to do this a lot, you could write a macro, but for one-time only the manual way is reasonably fast.
    Steve

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

    Re: Separating duplicate records. (XP & 2003)

    I have written a macro to do this, but I have not finished debugging it and I have to leave for an appointment. I see that Steve has given you a non macro solution. If you still want the macro solution, I will debug the macro when I return.
    Legare Coleman

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

    Re: Separating duplicate records. (XP & 2003)

    The VBA Procedure below will create a new worksheet named Duplicates if it does not already exist, copy all of the duplicate rows to that worksheet, and then sort that worksheet on column A:

    <code>
    Public Sub SepDups()
    Dim oSrc As Worksheet, oDst As Worksheet
    Dim I As Long, J As Long, lIMax As Long
    Application.ScreenUpdating = False
    Set oSrc = ActiveSheet
    On Error Resume Next
    Set oDst = Worksheets("Duplicates")
    On Error GoTo 0
    If oDst Is Nothing Then
    Set oDst = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
    oDst.Name = "Duplicates"
    oSrc.Activate
    End If
    J = 0
    lIMax = Range("A65536").End(xlUp).Row - 1
    For I = 0 To lIMax
    If WorksheetFunction.CountIf(Range(Range("A2"), Range("A2").Offset(lIMax, 0)), Range("A2").Offset(I, 0)) > 1 Then
    Range("A2").Offset(I, 0).EntireRow.Copy
    oDst.Paste Destination:=oDst.Range("A1").Offset(J, 0)
    J = J + 1
    End If
    Next I
    Range(oDst.Range("A1"), oDst.Range("IV1").Offset(J - 1, 0)).Sort _
    Key1:=oDst.Range("A1"), Order1:=xlAscending, Header:=xlNo
    Application.ScreenUpdating = True
    End Sub
    </code>
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Separating duplicate records. (XP & 2003)

    ---edited by gocush after testing

    Here's another method:

    If you don't want to REMOVE the duplicates automatically, but instead
    create a new list of unique items, the following code will populate your
    named range with UNIQUE items from list A

    <pre>Sub CreateUniqueList()
    '''''''''''''''''''''''''''''''''''''''''''''
    'Requires a reference to MS Scripting Runtime
    'In the VBA Editor, click on
    'Tools>References>Microsoft Scripting Runtime
    ''''''''''''''''''''''''''''''''''''''''''''''

    Dim Dict As Scripting.Dictionary
    Dim J As Long
    Dim oCell As Range
    Dim Source As Range
    Dim Dest As Range

    Set Source = Range("MyListA") 'a Named Range
    Set Dest = Range("UniqueList") 'another Named Range

    Set Dict = New Dictionary
    J= 1
    With Dict
    For Each oCell In Source
    If Not .Exists(oCell.Value) Then
    .Add Key:=oCell.Value, Item:=J
    Dest(J) = oCell.Value
    J= J + 1
    End If
    Next oCell
    End With

    Shutdown:
    Set Dict = Nothing

    End Sub</pre>



    To get a list of DUPLICATES, change the code to:

    <pre> Set Dest = Range("Dupes")

    and the following

    If Not .Exists(oCell.Value) Then
    .Add Key:=oCell.Value, Item:=J

    Else
    Dupes(J) = oCell.Value
    J= J + 1
    End If</pre>



    You can then manually review these to determine if you wanted them removed
    from your List A -- I think this is what you asked for in you post.

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Separating duplicate records. (XP & 2003)

    Hi

    I would like to use this code to create a unique List but I am having trouble editing the macro to section below. it stops here Dupes(J) = oCell.Value

    Any help would be much appreciated


    To get a list of DUPLICATES, change the code to:

    Set Dest = Range("Dupes")

    and the following

    If Not .Exists(oCell.Value) Then
    .Add Key:=oCell.Value, Item:=J

    Else
    Dupes(J) = oCell.Value
    J= J + 1
    End If

    Braddy
    If you are a fool at forty, you will always be a fool

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separating duplicate records. (XP & 2003)

    Should be

    Dest(J) = oCell.Value

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Separating duplicate records. (XP & 2003)

    Hi Hans

    Made the change as you suggested but it now stops at Next oCell

    Sub CreateDupesList()
    '''''''''''''''''''''''''''''''''''''''''''''
    'Requires a reference to MS Scripting Runtime
    'In the VBA Editor, click on
    'Tools>References>Microsoft Scripting Runtime
    ''''''''''''''''''''''''''''''''''''''''''''''

    Dim Dict As Scripting.Dictionary
    Dim J As Long
    Dim oCell As Range
    Dim Source As Range
    Dim Dest As Range

    Set Source = Range("MyListA") 'a Named Range
    Set Dest = Range("Dupes") 'another Named Range

    Set Dict = New Dictionary
    J = 1
    With Dict
    If Not .Exists(oCell.Value) Then
    .Add Key:=oCell.Value, Item:=J

    Else
    Dest(J) = oCell.Value
    J = J + 1
    End If
    Next oCell
    End With

    Shutdown:
    Set Dict = Nothing

    End Sub

    Braddy
    If you are a fool at forty, you will always be a fool

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separating duplicate records. (XP & 2003)

    If you take 5 seconds to compare your code to that posted by GoCush, you'll see that you have omitted a line.

Page 1 of 2 12 LastLast

Posting Permissions

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