• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

How do i delete a duplicate row by column in an excel spreadsheet?

Hello Experts,

I have 3 columns: First Name, Last Name, and Email

I'm trying to delete an entire row if there is a duplicate email.
0
Jazz24
Asked:
Jazz24
  • 3
  • 2
  • 2
  • +1
1 Solution
 
r_bielakCommented:
0
 
Dave BrettCommented:
This code runs on column A, B, C
the starting column is set by
Set rng1 = Intersect(Columns("A"), ActiveSheet.UsedRange)
the delete string by
   tempStr = cel.Value & cel.Offset(0, 1).Value & cel.Offset(0, 2).Value
that sticks A & B & C together  - Offset(0,1) says move 1 column to the right of A etc
Alternatively use my Duplicate Master addin from http://xldynamic.com/source/xld.DupMaster.html
  • download and install the tool
  • select your 3 columns, then run the tool via Tools ... Duplicate Master
  • search option ... row search
  • output choice .... delete duplicates
  • delete duplicate options ... tick ' Completely remove row (not just contents)
Cheers
Dave
 


Sub RemoveDuplicateEntriesMultiRow()
 
    Dim cel As Range, rng1 As Range, rng2 As Range
    Dim MyDic As Object
    Dim tempStr As String
    Set MyDic = CreateObject("Scripting.Dictionary")
    Application.ScreenUpdating = False
    Set rng1 = Intersect(Columns("A"), ActiveSheet.UsedRange)
 
    For Each cel In rng1
        tempStr = cel.Value & "|" & cel.Offset(0, 1).Value & "|" & cel.Offset(0, 2).Value
        If tempStr <> vbNullString Then
            If Not MyDic.exists(tempStr) Then
                MyDic.Add tempStr, cel.Row
            Else
                If Not rng2 Is Nothing Then
                    Set rng2 = Union(rng2, cel)
                Else
                    Set rng2 = cel
                End If
            End If
        End If
    Next cel
    If Not rng2 Is Nothing Then rng2.EntireRow.Delete
    Application.ScreenUpdating = True
    Set MyDic = Nothing
End Sub

Open in new window

0
 
Saurabh Singh TeotiaCommented:
I assumed your duplicate emails are in C Column..and you want to delete just 1 entry of the duplicates..then use the following code..and it will do what you are looking ...
Saurabh...

Sub ddelete()
    Application.ScreenUpdating = False
    Dim i As Long, rng As Range
    Set rng = Range("C1:C" & Cells(65536, "c").End(xlUp).Row)
 
    i = 1
    Do Until i > Cells(65536, "c").End(xlUp).Row
        If Application.WorksheetFunction.CountIf(rng, Cells(i, "C").Value) > 1 Then
            Rows(i).delete
        Else
            i = i + 1
        End If
    Loop
 
    MsgBox "Done"
    Application.ScreenUpdating = True
End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jazz24Author Commented:
Thank you that code worked. what if i have multiple worksheets and one to delete worksheet 1 and keep on worksheet 2
0
 
Saurabh Singh TeotiaCommented:
This will delete your duplicates from all sheets of your workbook...leaving sheet2
Saurabh...


Sub ddelete()
    Application.ScreenUpdating = False
    Dim i As Long, rng As Range, ws As Worksheet
    Set rng = Range("C1:C" & Cells(65536, "c").End(xlUp).Row)
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet2" Then   'I Assumed you dont want to delete your duplicate emails from sheet2
            ws.Select
            i = 1
 
            Do Until i > Cells(65536, "C").End(xlUp).Row
                If Application.WorksheetFunction.CountIf(rng, Cells(i, "C").Value) > 1 Then
                    Rows(i).delete
                Else
                    i = i + 1
                End If
 
            Loop
        End If
    Next ws
 
 
    MsgBox "Done"
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
Dave BrettCommented:
The addin will delete duplicate rows between sheets - but as it works left sheet accross, top of sheet down it will delete both intra and inter sheet duplicates rows
Neither of the macros above will delete duplicate rows between sheets (if that was your intent), although they can run on multiple sheets. Can you pls expand on your multiple sheet query?
Cheers
Dave
 
0
 
Jazz24Author Commented:
Thank you! it worked
0
 
Dave BrettCommented:
thx for the grade :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now