Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Compare Ranges And Delete Duplicates

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Compare Ranges And Delete Duplicates

    Hello

    In the attached sheet, some of the rows are repeated, whcih i want to detect and delete. Defined two ranges rng1 and rng2. Is there a way easire way to comapre rng1 and rng2 without comparing each elements of rng1 and rng2?
    Code:
    Sub compare()
    Dim c As Integer, r As Integer, rng1 As Range, rng2 As Range
    r = 1
    Set rng1 = Range(Cells(16 + r, 1), Cells(16 + r, 14))
    Set rng2 = Range(Cells(16 + r + 1, 1), Cells(16 + r + 1, 14))
    ' r will be incremented to comapre other ranges in a loop
    End Sub
    Attached Files

  • #2
    Re: Compare Ranges And Delete Duplicate

    Combine the 2 ranges and use AdvancedFilter for Uniques only.

    Comment


    • #3
      Re: Compare Ranges And Delete Duplicates

      Your sample file has data records from row 16 to 114. Each record consists of 14 columns (A through N). I filtered it and found every record to be unique if all columns are considered. What constitutes a unique record??
      Bill
      Tip: To avoid chasing code always use Option Explicit.

      Comment


      • #4
        Re: Compare Ranges And Delete Duplicates

        Hello
        Sorry, for the confusion. I am looking for columns 1 to 11. For example rows 19 and 20 are same from column1 to 11. In this case I want to dlete row 20.
        Auto Merged Post Until 24 Hrs Passes;

        Hi
        With the following code, I will be able to take out the unique:
        Code:
        Range("A16:K114").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("p16"), Unique:=True
        Now, the unique data in the range ("A16:K114") has been placed at ("p16"). But, I am missing corrsponding information from columns 12 to 14 (Corresponding data from columns L, M and N should go oto Columns AA, AB and AC). How to get this? Once I get this, I want to copy data in range(P16:aa16.end(xl)down) to range("a16").
        Last edited by ljoseph; April 1st, 2008, 20:10. Reason: Auto Merged Doublepost

        Comment


        • #5
          Re: Compare Ranges And Delete Duplicates

          Hi,

          Sub kTest()
          Dim a, i As Long, j As Long, c As Long, w(), CS As String
          a = Range("a17:n" & Range("a" & Rows.Count).End(xlUp).Row)
          ReDim w(1 To UBound(a, 1), 1 To UBound(a, 2))
          With CreateObject("scripting.dictionary")
          .comparemode = vbTextCompare
          For i = 1 To UBound(a, 1)
          If Not IsEmpty(a(i, 1)) Then
          For c = 1 To 11: CS = CS & ";" & a(i, c): Next
          If Not .exists(CS) Then
          j = j + 1
          For c = 1 To UBound(a, 2)
          w(j, c) = a(i, c)
          Next
          .Add CS, Nothing
          End If
          End If
          CS = ""
          Next
          End With
          With Range("a17")
          .Resize(UBound(a, 1), UBound(a, 2)).ClearContents
          .Resize(j, UBound(a, 2)).Value = w
          End With
          End Sub


          HTH
          Kris

          ExcelFox

          Comment


          • #6
            Re: Compare Ranges And Delete Duplicates

            Use Advance Filter

            Insert a new sheet.
            Select range A16:K114 on your data sheet
            From the main menu select Data/Filter/Advanced Filter
            From the popup window check the Unique Records Only box
            Press OK

            The duplicate records are now hidden
            Copy and paste the filtered records to the new sheet.
            Auto Merged Post Until 24 Hrs Passes;

            Code:
            Range("A16:K114").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
            Range("A16").CurrentRegion.Copy Range("P16")
            Last edited by Bill Rockenbach; April 1st, 2008, 23:34. Reason: Auto Merged Doublepost
            Bill
            Tip: To avoid chasing code always use Option Explicit.

            Comment


            • #7
              Re: Compare Ranges And Delete Duplicates

              Thanks for your help.
              This works fine.

              Comment

              Trending

              Collapse

              There are no results that meet this criteria.

              Working...
              X