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

Thanks
mtthompsonsAsked:
Who is Participating?
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
                Else
                    Set myRange = Union(myRange, c)
                End If
            
        Next c
        myRange.Select
   MsgBox r & " duplicates found", vbInformation, "Duplicate search"
   On Error GoTo 0
End Sub

Open in new window

0
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?
0
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
0
Roy CoxGroup Finance ManagerCommented:
Can you attach the workbook with the code in and I'll check it
0
Rob HensonFinance AnalystCommented:
Alternative without VBA.

In a separate column put the following formula:

=COUNTIF(A$1:A1,A1)  

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.

Thanks
Rob H
0
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
0
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.

Thanks
Rob H
0
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
        Else
            Rows(i).Delete
        End If
    Loop
    Application.StatusBar = ""
End Sub

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
I actually should have said Filter to a different location
0
ProfessorJimJamCommented:
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.
iG8NZ.jpg
0

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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.