Delete duplicate row based on 2 cells criteria

Posted on 2011-05-04
Last Modified: 2012-05-11
Hi all,

I have an excel sheet (just like the below where I have some rows that have the same data in column A("Old") and B("New"). I would like for it to keep the first instance of an identical row and delete whenever else there is a duplicate where teh values in both columns had an exact replica in a previous row. I can't seem to find it on any search engines. For clarity, i have added in te table below a note at the ned of each row of which rows to keep and which to delete. The actual data I wish to process is over 45,000 lines so a sumproduct is out of teh question as it completely blocks my PC each time it updates.

i am using Excel 2007 and have some knowledge of VBA.

Old       New      
1AB      645      Keep
1AB      645      Delete
1AB      654      Keep
1AB      654      Delete
2AD      456      Keep
2AD      456      Delete
2AD      546      Keep
2AD      546      Delete

Question by:touyets17
    LVL 30

    Expert Comment

    >>>i am using Excel 2007 and have some knowledge of VBA.

    You don't need VBA For this.

    Click on Data Tab ~~> Remove duplicates and the select the relevant columns :)

    LVL 30

    Accepted Solution

    If you still like a macro then use this. Please amend as applicable. :)

    Sub Sample()
        Dim LastRow As Long
        LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        Sheets("Sheet1").Range("$A$1:$C$" & LastRow).RemoveDuplicates _
        Columns:=Array(1, 2), Header:=xlYes
    End Sub

    Open in new window

    LVL 41

    Expert Comment

    This code will remove all but the first instance of duplicates...

    Assumes the data is in columns A:B, and the first row is a header...

    Here's the code:

    Sub keepOnlyFirst()
    Dim myCell As Range, tmpRng As Range
    Dim testVal1 As String, testVal2 As String
    Dim i As Long, lastRow As Long
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
        i = 2
            Set myCell = Cells(i, 1)
            If myCell.Value = testVal1 And myCell.Offset(0, 1).Value = testVal2 Then 'check Old and New for duplicates
                i = i - 1
                lastRow = lastRow - 1
                testVal1 = myCell.Value
                testVal2 = myCell.Offset(0, 1).Value
            End If
            i = i + 1
        Loop Until i > lastRow
    End Sub

    Open in new window

    See attached spreadsheet demonstration.


    LVL 41

    Expert Comment

    Sid - very nice!


    Author Closing Comment


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now