Delete 1 of the duplicates from excel

Hi All,

I have column "A" and "B" with data if a duplicate matched in column "A" than delete one row of duplicate and leave the other

Anyone can help with a macro

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
If you are using one of the newer versions of Excel you don't need a macro, there is a Remove Duplicates function in the Data tab

Try this

Sub SelectDuplicates()
    On Error Resume Next
    Dim c As Range
    Dim r As Long
    Dim myRange As Range
        For Each c In Selection
            If Application.CountIf(Selection, c) > 1 Then
'                If r = 0 Then
                    Set myRange = c
                    r = r + 1
                    Set myRange = Union(myRange, c)
                End If
        Next c
   MsgBox r & " duplicates found", vbInformation, "Duplicate search"
   On Error GoTo 0
End Sub

Open in new window

mtthompsonsAuthor Commented:
Thanks i have excel 2011 but with the remove duplicates it will delete the cells and would be an issue so need to delete the whole rows and just 1 which is a duplicate

Will the macro do this?
Roy CoxGroup Finance ManagerCommented:
The macro will remove all duplicates by removing the entire roe leaving only unique items. Try it on a dummy file
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mtthompsonsAuthor Commented:
Macro ran for few hrs but did not delete the duplicate rows
Column "A" has this

Anandan Sivamani
Anandan Sivamani

So one complete row should be deleted and one left alone
Roy CoxGroup Finance ManagerCommented:
Can you attach the workbook with the code in and I'll check it
Rob HensonFinance AnalystCommented:
Alternative without VBA.

In a separate column put the following formula:


Note the absolute reference for row 1, adjust the column reference if required and copy down; it will expand the count range.

The first occurence of a value will get result 1 and subsequent entries will get incremented. Apply a filter to the data and hide all the rows with 1 as result. You can then select the remaining rows as a block and delete rows. A message will ask to confirm deleting entire row. Disable the filter and the unique values will re-appear.

Rob H
Roy CoxGroup Finance ManagerCommented:
I would think that you could actually use AdvancedFilter to filter for Unique values as well

Data Tab -> AdvancedFilter. Choose Filter in Place and Unique Items
Rob HensonFinance AnalystCommented:
Roy - Adv Filter will indeed Filter in place for unique items but that would just hide the duplicates rather than remove them.

Rob H
Saurabh Singh TeotiaCommented:
You can select your worksheet and run this code it will do what you are looking for.. In additional it will also tell you which row it's working on so that you know macro status...

Sub deletedata()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Dim i As Long
    i = 1
    Do Until i > Cells(Cells.Rows.Count, "A").End(xlUp).Row
        Application.StatusBar = "Working on Row Number--> " & i & "  % Complete--> " & Format(i / Cells(Cells.Rows.Count, "A").End(xlUp).Row, "0.00%")
        If Application.WorksheetFunction.CountIf(Range("A1:A" & i), Cells(i, "A").Value) = 1 Then
            i = i + 1
        End If
    Application.StatusBar = ""
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
I actually should have said Filter to a different location
ProfessorJimJamMicrosoft Excel ExpertCommented:
you could use the following

Sub RemoveDupes() 
    Dim lastrow As Long 
    Application.ScreenUpdating = False 
    With Sheet1 
        lastrow = .Cells(Rows.Count, "A").End(xlUp).Row 
        ActiveSheet.Range("$A$1:$B$" & lastrow).RemoveDuplicates Columns:=1, Header:=xlYes 
    End With 
    Application.ScreenUpdating = True 
End Sub 

Open in new window

or without VBA   see the picture.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.