Solved

REMOVING ALL DUPLICATES

Posted on 2013-05-14
24
269 Views
Last Modified: 2013-05-15
I am attaching my Excel sheet with the two columns that I want the duplicates and the originals deleted. So, if there is a duplicate remove both.

The columns are A and AR that have the data.  I have stripped all other information concerning the patient out.

KR
Copy-of-deletedupes.xlsm
0
Comment
Question by:kvrogers
  • 12
  • 11
24 Comments
 
LVL 4

Expert Comment

by:Kent Fichtner
Comment Utility
If you only need to do this once, I would recommend you use a conditional format to find the duplicates.  Then you can sort based on the color of the cell.  From there you can just highlight all the colored duplicates and delete them.

If you want a longer solution or one that is faster and can be used more often, you may have to wait for someone who can write a macro.

Hope that helps a little.
0
 

Author Comment

by:kvrogers
Comment Utility
i think I do need a macro as this is going to be a long term solution.  What I am doing is actually combining two files into the one sheet and then deleting any and all duplicates.

I saw a similar question but did not know how to adjust the macro to fit my needs.

Thanks for your help though.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
If the workbook you posted the data in column A looks like


MG0000000009
MG0000000009
MG0000000012

and the data in AR looks like
5
3
9
123

Should I assume that the "9" in column AR is a duplicate of the "MG0000000009" in column  A? If so then do you want all 3 to be deleted? And should I delete just the cells that contain the data or all 3 rows?
0
 

Author Comment

by:kvrogers
Comment Utility
If I use REMOVE DUPLICATES and select columns A and AR it will return the UNIQUE value that is left but I would like this value removed as well.

So if Column A and Column AR are duplicates remove ALL.

kr
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Sorry, but while I'm sure you just tried, could you please specifically answer the 3 questions in my previous post?
0
 

Author Comment

by:kvrogers
Comment Utility
The 9 in column AR is a TRANSACTION # not a duplicate account number.  If Column A (the Acct# and column AR ( the transaction number) are BOTH duplicates then delete the entire rows.
 
The formula should be:  if the account number has a duplicate transaction number then delete them both from the sheet.

Example:

Acct#                       Txn#
MG0000000001      128
      
MG0000000001      128
      
MG0000000001      351
      
MG0000000001      578


Rows 1 and 2 are duplicates and both rows should be deleted.  Sorry, I hope this is a better explanation.  

KR
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Yes, much better, thanks.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Can I sort the data and remove blank lines?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I'm sorry but I have to apologize to you. I just lost the code that I was working on and I don't have time today to work on it any more. Sorry.
0
 

Author Comment

by:kvrogers
Comment Utility
Thanks for trying.  

Here is what I found in another question but didn't know how to adjust it.

Sub deletedupes()
    Dim rng As Range
    Dim r As Long
    Dim tag As Long
   
    tag = 0
    Set rng = Range(Sheets("Sheet1").[a1], Sheets("Sheet1").[a65536].End(xlUp))

    rng.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
       
    If Cells(1, 1) = Cells(2, 1) Then
        tag = 1
    End If
   
starte:
    For r = Sheets("Sheet1").[a65536].End(xlUp).Row To 2 Step -1
        If Cells(r, 1) = Cells(r - 1, 1) Then
            If r <> 2 And Cells(r - 1, 1) = Cells(r - 2, 1) Then
                Rows(r - 2).Delete
            End If
            Rows(r).Delete
            If r <> 1 Then
                Rows(r - 1).Delete
            End If
            GoTo starte
        End If
    Next r
   
    If tag = 1 Then
        Rows(1).Delete
    End If
   
End Sub
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I did have some time after all. This takes 6 minutes to run on my PC.  Make a copy of your sheet before you run it. It deletes all rows where A and AR are the same including the row where each unique combination is found. In other words with this data:

A     AR
11     aaa
11     aaa
11     bbb
12     ccc
12     ccc

you would wind up with
11     bbb

Debug.Print Now()
Dim Rng As Long, i As Long, j As Long
Dim strColA As String
Dim strColAR As String
Dim bFound As Boolean
Dim lngRow As Long
Debug.Print Now()

Application.ScreenUpdating = False
Application.Cursor = xlWait
Rng = Application.ActiveSheet.UsedRange.Rows.Count
    Columns("A:AR").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "A2:A" & Rng), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "AR2:AR" & Rng), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1:AR" & Rng)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
Rng = Range("A" & Rng).End(xlUp).Row


For i = Rng To 2 Step -1
    Cells(i, 1).Select
    strColA = ActiveCell
    lngRow = ActiveCell.Row
    strColAR = ActiveCell.Offset(0, 43)
    ActiveCell.Offset(-1, 0).Select
    If ActiveCell = strColA And ActiveCell.Offset(0, 43) = strColAR Then
            ActiveCell.Offset(0, 75).Value = "delete"
            Cells(lngRow, 1).Value = "delete"
    End If
Next
For i = Rng To 2 Step -1
    If Cells(i, 76).Value = "delete" Then
        Cells(i, 76).EntireRow.Delete
    End If
Next
Application.ScreenUpdating = True
Debug.Print Now()
Application.Cursor = xlDefault
Beep
MsgBox "Done"

End Sub

Open in new window

0
 

Author Comment

by:kvrogers
Comment Utility
To use this do I:
1.  Go to create MACRO
2.  Name macro DELETEDUPES
3.  Paste your formula above in.
4.  To run go to view Macros and select DELETEDUPES
5.  Click RUN.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
You can also do it this way.

1.    Go to Visual Basic
2.    Copy my complete Sub into Module1
3.    Go back to Excel and click Developer|Macros
4.    Highlight deletedupes and then click Options
5.    Assign a Shortcut Key like 'z' and click OK

After that all you need to do anytime you want to execute the code is to click Ctrl+z
0
 

Author Comment

by:kvrogers
Comment Utility
Marty:
Do you think a VB course at the local community college would help me, or UMSL (University of St. Louis) has a one day course you can take?  Or could you recommend a good beginner book to read.
Kr
0
 

Author Closing Comment

by:kvrogers
Comment Utility
Got back to me very quickly and I appreciate that.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I've got no knowledge of what those schools offer but there's a lot of stuff online for free concerning VBA, VB6 (my speciality) and VB.Net. Just Google for example VBA tutorial. If you want to write VB code outside of Excel you'd want to use VB.Net and I believe you can get a starter addition for free.

I'm glad I was able to help.

Select the 'About Me' tab in my profile and you'll find a link to a debugging article that may interest you. It applies to both VBA and VB6.

Marty - MVP 2009 to 2013
0
 

Author Comment

by:kvrogers
Comment Utility
Thanks,
KR
0
 

Author Comment

by:kvrogers
Comment Utility
I ran the maco doing a CTRL=z and it ran but I never noticed that the report now shows the word DELETE in column A.

I thought this would delete the entire LINE is Column A and column AR were duplicates.  Does the DELETE mean this line should have been eliminated?

KR
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Sorry about that. Line 40 should be

      Cells(lngRow, 75).Value = "delete"

rather than

      Cells(lngRow, 1).Value = "delete"
0
 

Author Comment

by:kvrogers
Comment Utility
PERFECT!!!  Thanks,
KR
0
 

Author Comment

by:kvrogers
Comment Utility
Well Almost.  I noticed in ROW BW there is the word DELETE.  What does that mean?

Also received the attached error message on close.
macro-error.PNG
deletedupes-removed.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Corrected code.
Sub DeleteAcct()
Debug.Print Now()
Dim Rng As Long, i As Long, j As Long
Dim strColA As String
Dim strColAR As String
Dim bFound As Boolean
Dim lngRow As Long
Debug.Print Now()

Application.ScreenUpdating = False
Application.Cursor = xlWait
Rng = Application.ActiveSheet.UsedRange.Rows.Count
    Columns("A:AR").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "A2:A" & Rng), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "AR2:AR" & Rng), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1:AR" & Rng)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
Rng = Range("A" & Rng).End(xlUp).Row


For i = Rng To 2 Step -1
    Cells(i, 1).Select
    strColA = ActiveCell
    lngRow = ActiveCell.Row
    strColAR = ActiveCell.Offset(0, 43)
    ActiveCell.Offset(-1, 0).Select
    If ActiveCell = strColA And ActiveCell.Offset(0, 43) = strColAR Then
        ActiveCell.Offset(0, 75).Value = "delete"
        Cells(lngRow, 76).Value = "delete"
    End If
Next
For i = Rng To 2 Step -1
    If Cells(i, 76).Value = "delete" Then
        Cells(i, 76).EntireRow.Delete
    End If
Next


Application.ScreenUpdating = True
Debug.Print Now()
Application.Cursor = xlDefault
Beep
MsgBox "Done"

End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
BTW I just realized that in Post ID: 39166552 I said

4.    Highlight deletedupes and then click Options

whereas I should have said

4.    Highlight DeleteAcct and then click Options

Hopefully you caught that.
0
 

Author Comment

by:kvrogers
Comment Utility
Sorry, no I didn't catch it.  I have applied the new code and it worked.  Sending to user to make sure that everyting is correct.

KR
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

10 Experts available now in Live!

Get 1:1 Help Now