Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Compare Ranges And Delete Duplicates

  1. #1
    Join Date
    17th July 2003
    Location
    Dallas
    Posts
    284

    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?
    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Compare Ranges And Delete Duplicate

    Combine the 2 ranges and use AdvancedFilter for Uniques only.

  3. #3
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    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.

  4. #4
    Join Date
    17th July 2003
    Location
    Dallas
    Posts
    284

    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:
    VB:
    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 at 20:10. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Compare Ranges And Delete Duplicates

    Hi,

    VB:
    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

  6. #6
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    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;

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

  7. #7
    Join Date
    17th July 2003
    Location
    Dallas
    Posts
    284

    Re: Compare Ranges And Delete Duplicates

    Thanks for your help.
    This works fine.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Compare And Copy Duplicates, Also Merge Other Columns
    By SmartKenya in forum Excel General
    Replies: 7
    Last Post: September 1st, 2010, 12:25
  2. Compare Two Rows & Highlight Duplicates
    By Petehorse in forum Excel General
    Replies: 5
    Last Post: August 1st, 2008, 23:17
  3. Compare 2 Lists & Highlight Duplicates
    By bsuym in forum Excel General
    Replies: 10
    Last Post: July 4th, 2007, 14:40
  4. Compare Columns for Duplicates
    By xlsNovice in forum Excel General
    Replies: 4
    Last Post: October 14th, 2005, 17:11
  5. Replies: 2
    Last Post: December 11th, 2004, 01:19

Bookmarks

Posting Permissions

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