Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Delete Both Duplicate Rows In Excel

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Basically I have two huge lists and need to do what the title of the thread says.

The first list is from about 3yrs ago and is very large

The second list is from recently and is about half the size.

I need to find all records on the SECOND list that are NOT on the first list (new records that have been added since the first list was generated). I do not care about records on the first list that are not contained in the second list.

I was thinking that if there is a way to delete both of the duplicate records, I would be left with only records that are only included on one list. Before the formula/macro was executed I would color code each list and simply remove all of the records from the original list, leaving me with only records contained in the second list but not the first.

I found this macro on excel and it isn't doing what I need it to do...

Code:

Sub DelDups()
'Determine the number of items in column B
 lastRow = Cells(Rows.Count, 2).End(xlUp).Row
On Error GoTo Done
'Loop through rows, starting at the bottom
  For delRow = lastRow To 1 Step -1
'If duplicates are found, delete both rows
  If Cells(delRow, 1) = Cells(delRow - 1, 1) Then
    Cells(delRow, 1).EntireRow.Delete
    Cells(delRow - 1, 1).EntireRow.Delete
   End If
 Next
Done:
End Sub


I ran that macro and it is not deleting records that appear in both lists.

And yes, before anyone asks, I made sure the data I wanted to filter was in column b.


Similar Excel Video Tutorials

Helpful Excel Macros

Filter Data in Excel to Display Records that Contain a Value Between Two Values - AutoFilter.
- This free Excel macro filters data to display only those records or rows that contain a value between two values. For e
Delete Duplicate Rows
- This macro will delete rows that appear twice in a list or worksheet. If two cells are identical, this macro will delete
Filter Data to Show Only the Bottom 10 Items in Excel - AutoFilter
- This Excel macro filters a selection of data in order to display only the bottom 10 items in the list or data set. This
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Filter Data to Show the Top 10 Items from a Data Set in Excel - AutoFilter
- This Excel macro filters data in Excel in order to display the top 10 items from the data set. The macro uses the autof

Similar Topics







Hey all,

I know what the end result is that this code deletes any row with 0 value in columns A B and C, but I am trying to understand how it works. Would anyone please clarify, I would really appreciate it.

Code:

Sub DelZero()
'Find last row with data
 For ColNum = 1 To 3
  NxtLastRow = Cells(Rows.Count, ColNum).End(xlUp).Row
   If NxtLastRow > LastRow Then LastRow = NxtLastRow
 Next
'Check Columns A:C for combination
 For DelRow = LastRow To 1 Step -1
  If (Cells(DelRow, 1) = 0 And Cells(DelRow, 2) = 0) And (Cells(DelRow, 3) = 0) Then
'Delete row if combination found
      Rows(DelRow).EntireRow.Delete
   End If
 Next
End Sub





Hi all

So I've gone through a sheet of records for a mailing list, deleting about 5k records from a list of 15k. Now I've got the original list of 15k, and a 'cleaned' list with 10k names. What I want is a list of the 5k names I deleted.

I've tried copying the 15k list and the 10k list onto the same spreadsheet, so now roughly 20k of those 25k records are duplicates, and the remaining 5k are the ones I want. Now I want to remove the duplicate records as well as the originals that were duplicated.

I thought I was onto something with the Advanced Filter button, which has a checkbox called "unique records only". But apparently that also leaves one of the two duplicate records on display, just as the "remove duplicates" button does.

Any suggestions?

Edit: BTW, the COUNTIF function apparently only applies to cells, not entire rows. And I need the entire ROW to be matching (I have records where the name cell is identical, but address cells aren't, etc, and those need to be treated as entirely different records).


I have a list with 55,000 rows (records).

A few hundred of these records are exact duplicates.

I need them removed.

When I use the 'Unique Records Only' check box on Advanced Filter, it doesn't appear to do a thorough job of removing all the duplicates.

Does anyone know of a relatively fast way to remove duplicate records, or maybe have some code that will do it?

Thanks,
Rob


I have three seperate excel spreedsheets. List 1 is the original with 6100 records. List 2 and 3 indicate records to be deleted from list 1. Other than utilizing Edit - Find - are there any other solutions?


Hi Guys

I hope someone can help with my VBA knowledge. The code below is meant to take a variable "txtEnquiryNo", find out which row contains this value on sheet "Project List", Copy the row to the next available row on sheet "LOG" -prior to deleting it from "Project List". Where I currently have Rows("191") & Rows("6") I need variables that refer to "delrow" & "activerows" respectively. Using variable names in the code gives me an error so this is probably not the best way to proceed.

Any help really appreciated.

Rgds

Sub DeleteLog()
If txtEnquiryNo "" Or txtProspectNo "" Then
ActiveWorkbook.Sheets("Project List").Activate
actualrows = 3
delrow = 3
While Cells(actualrows, 1) ""
actualrows = actualrows + 1
Wend
Dim Seq As String
For i = 3 To actualrows
Seq = Cells(i, 22)
If Seq = txtEnquiryNo.Value Then
delrow = i
End If
Next
End If
' Code to copy to Log sheet and delete from Project List
Rows("191").Copy
ActiveWorkbook.Sheets("LOG").Activate
actualrows = 4
While Cells(actualrows, 1) "" Or Cells(actualrows, 2) "" _
Or Cells(actualrows, 3) "" Or Cells(actualrows, 4) "" _
Or Cells(actualrows, 5) "" Or Cells(actualrows, 6) ""
actualrows = actualrows + 1
Wend
Rows("6").Select
ActiveSheet.Paste
End Sub


RESOLVED: Solution = get someone elses more powerful computer to do it.

Hi Guys,

I've tried searching around and I'm possibly not using the right search criteria but haven't found what I need yet.

Basically, I have a WB that has a list of emails in Column A Sheet 1 of about 4000 records, I then have a list of emails in Column B Sheet 2 of about 11000 records.

I need a macro to delete the duplicates out of Sheet 2 if they've already appeared in Sheet 1.

At the moment I'm currently using this

Quote:

Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer
' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count
' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

Which has previously worked fine for lists of about 20 or so, but now if I up the values to my large amount of rows it crashes my computer.

Can anyone help??

Thanks


Hi

I was wondering if somebody could offer me some help with the issue below. I am sure it could be done easily using the unique filter, but my brain can't work out how!

I have 2 lists of ID numbers, List A consists of 500 records and is in range A1:A500. List B consists of 196 records and is in range B1:B196.

The 196 records in list B also exist in list A. These are the important ones - and the ones which I need to keep. I am looking for a way to identify the remaining 304 records in list A and delete them, keeping only the original 196.

I hope this is a reasonalbly clear explanation - any help would be appreciated

Rich


I have a worksheet for which I filter to find empty records (rows).
This worksheet is volatile ... it will always have a variable number of records, and thus a variable number of empty records (rows).
In my current macro, if applies the filter presenting only those empty records, however, the rows are sporadic ... ie 12, 14, 100, 132, 377, 401. As it is now I simply highlight these rows and delete to get rid of them.
But, since these rows are volatile, the macro will only always delete 12:401, whether empty or not.

Here is my current code:

'Delete empty records (0011)
Selection.AutoFilter Field:=9, Criteria1:="0011"
Rows("337:837").Select
Selection.Clear

How do I delete rows from a filtered list in a variable environment. In all cases, I will only ever want to delete the first "x" number of rows from the filtered data?

Jenn


I have a dataset of approx 260K+ records; I wish to delete records from this dataset based on my criteria range(a range of 25 items). With smaller datasets I would normally just apply the criteria to the dataset and then delete the resulting records. However, my criteria return approximately 30-35K records in this larger dataset. When I select these and attempt to "Delete Row", I get a message that the range is too large or too complex and excel cannot delete the selected rows.

I considered criteria to identify records I want to keep, but I'm dealing with medications delivered in a hospital setting and the list could literally be hundreds of items long and could miss new meds added to the formulary that I'm not aware of. Just much more efficient to identify the items that don't belong on the list than those that do.

Any alternative approaches to this problem would be appreciated.

I am trying to put this in VBA once a strategy for identifying and deleting the records is identified.

Thanks, CC


Hi All,

I've searched the forum and could not find a way to delete duplicate records greater than 100 records in each instance. I have over 330,000 records in Excel 2007 and the majority of duplicate records over 100 records but I only want to keep the first 100 dup records in each unique value instance.

Not sure if this is even possible, where most users want to see the single record whereas I'm looking to see the first 100 items of each.

Data sample:
Type Symbol Date
c lbu 01/01/10
c lbu 02/01/10
c lbu 03/01/10
x mmn 06/01/10
x mmn 08/01/10
etc... 300,000 plus records

Thanks a bunch for all the help!


My Excel sheet have 60000 records. I want to delete a list of records from the sheet. The list is from one of the column of the sheet and has about 2000 values.

Myquestion is:how to find multiple records in an excel sheet using one criteria and delete the found records

I'd appreciate any help/tutorial link.
thanks in advance.


Hi. I have a problem I'm trying to solve. I am merging two lists. Both are lists of names. There are some people on both lists. I want to eliminate the records of the people who were only listed in one of the original two lists and be left only with the list of names who are common to both lists.

Each person on both lists has an ID number that I can use to compare. But while Excel seems to make it easy to eliminate duplicates, I want to do the opposite. I want to eliminate the records of people who are not on both lists and keep only the diplicate records in the spreadsheet.

The only way I've been able to figure out how to do this is to go through manually, mark a column for the duplicates, then sort on that column to collect all the duplicate names and delete the rest. This is very time consuming with a list that contains thousands of names.

Does anyone have a method to identify and segregate the duplicates in a spreadhseet so I can delete the rest?

Thanks.


hi,

im using windows 7, office 2007

records= the name of the sheet were all my data is on (for list populating and storing data from the user)

F= userform name

SOFTWARELIST = a listbox populated with the data (S3:S10) from "records" (using additem)


i have written the following code that has an if statment.
for some unknown reason to me, the statement does not work, even thought all the condition are there to trigger it.

the code should:
get the value of the listbox (SOFTWARELIST) ,which the user selected from the list, then go to sheet "records" and "look" for the same value in a cell - from a range (which MUST be there, because is appeared/added to that list), and delete that cell, and the next cell to the left, and finaly, delete that value from the list.

Code:

Private Sub CommandButton9_Click()

S = Sheets("records").Range("S1").End(xlDown).Row

For Each cell In Sheets("records").Range("S3:S" & S)
If cell.Value = F.SOFTWARELIST.Value Then
    CR = cell.Row
    Sheets("records").Cells(CR, 19).Delete Shift:=xlUp
    Sheets("records").Cells(CR, 20).Delete Shift:=xlUp
    
    ind = Me.SOFTWARELIST.ListIndex
    Me.SOFTWARELIST.RemoveItem (ind)
    
    End If
Next cell

End Sub


i have no idea why it won't work.

it's driving me crasy...!!

thank you.


Hi everyone! I can't figure out how to entirely remove duplicate records from a list. I do not just want to delete duplicates - that's easy. Pretend that I have two lists - the first is 1 2 3 4 5 6 7 8 9, and the second is 3 6 9. I need a function that will return 1 2 4 5 7 8 after the list is filtered. I can't figure out how to do this at all and I can't find it anywhere on the internet. Any help is greatly appreciated. Thank you!


Hi,

Got a question about a macro which deletes duble records and blanks rows in a sheet. I got the following macro, but it wont work.

Code:

 
Application.Calculation = xlManual
On Error Resume Next
Rij = ActiveCell.Row
kolom = ActiveCell.Column
Do While Cells(Rij, kolom)  ""
If Trim(Cells(Rij, kolom).Value) = Trim(Cells(Rij + 1, kolom)) Then
Rows(Rij).Select
Selection.Delete Shift:=xlUp
Else
Rij = Rij + 1
End If
Loop
Cells(1, kolom).Select
Application.Calculation = xlAutomatic


My records are all in column A with blank rows in between it and there are some records that are more times in the list. When i select cell A1 and run the macro, nothing happens...

Does somebody knowes why or knowes another macro that will delete dubble records and blank rows?

Kind regards,

Zwans


I have attempted to create a macro which scan cells from column D to find duplicates and move the row where the 'second' duplicate is found (i.e. the one with the highest row number) to first empty row, i.e. right at the bottom of the list.

As an example, the worksheet has no more than 100 rows, the macro starts to look for the value in D1 and compare it to D2, if the values are the same, then row 2 is moved down to row 100. So on so forth for the remaining cells in column D until a blank row is met, then the macro simply deletes all empty row.

Here is a sample of the code I have been using:

Code:

Sub Move_Duplicates()

Dim ws1 As Worksheet: Set ws1 = ActiveSheet
Dim iListCount As Integer
Dim iCtr As Integer
Dim r As Long

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = True

' Get count of records to search through.
iListCount = Range("B65536").End(xlUp).Row
ws1.Range("D1").Select

' Loop until end of records.
Do Until ActiveCell = ""
   ' Loop through records.
   For iCtr = 1 To iListCount
      ' Don't compare against yourself.
      ' To specify a different column, change 1 to the column number.
      If ActiveCell.Row <> ws1.Cells(iCtr, 4).Row Then
         ' Do comparison of next record.
         If ActiveCell.Value = ws1.Cells(iCtr, 4).Value Then
            ' If match is true then delete cut row and paste further down 100 rows
            Rows(iCtr).Cut
            Rows(Offsetcount + 100).Insert Shift:=xlDown
               ' Increment counter to account for deleted row.
               iCtr = iCtr + 1
         End If
      End If
   Next iCtr
   ' Go to next record.
   ActiveCell.Offset(1, 0).Select

Loop

For r = Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1
    If Cells(r, 2) = "" Then Rows(r).Delete
Next r

'Application.ScreenUpdating = True

End Sub


The macro runs however it does not provide a consistent outcome according to the source data I use. For example the macro moves rows to the bottom of even if there are no duplicates.

Any help would be appreciated.

Thanks,
Antoine


Afternoon,

I've been cobbling together bits and pieces of VBA from all over the show and have ended up with the below:
Code:

Sub sort()

ActiveWorkbook.Unprotect Password:="m4n1f35t"
Application.DisplayAlerts = False
 For ColNum = 1 To 6
  NxtLastRow = Cells(Rows.Count, ColNum).End(xlUp).Row
   If NxtLastRow > LastRow Then LastRow = NxtLastRow
 Next
 For DelRow = LastRow To 1 Step -1
  If (Cells(DelRow, 1) = 0 And Cells(DelRow, 2) = 0) Or _
     (Cells(DelRow, 1) = "" And Cells(DelRow, 2) = "") Or _
     (Cells(DelRow, 1) = 0 And Cells(DelRow, 2) = "") Or _
     (Cells(DelRow, 1) = "" And Cells(DelRow, 2) = 0) Then
      Rows(DelRow).EntireRow.Delete
      End If
      Next
    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.End(xlUp).Select
    Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).RemoveDuplicates Columns:=2, Header:= _
        xlYes
    Range("A2").Select
    ActiveWorkbook.Protect Password:="m4n1f35t"
    ActiveWorkbook.Save
    Application.DisplayAlerts = True

End Sub


I get the 1004 Paste Special Failure from this.
However, if I remove the code to unprotect and protect the sheet it works fine - so I can only assume that the these sections are causing this issue?



If anyone could point me in the right direction I'd appreciate it - I'm fresh meat for the VBA grinder so small words please.

Cheers

Andy


I am novice to Excel and VBA.

I need to be able to compare Sheet1-Column A to Sheet2-Column A
for duplicates and then delete the ENTIRE row in Sheet2 of the duplicates.
There may be more than one incidence of the duplicate in
Sheet2-Column A
I need to keep the header intact too.

I am looking for any macro solution.

This link below of Sample 2 almost works for my needs except
it does not go beyond row 100 and it does not delete the entire row.
It also deletes the header and doesn't delete the second incidence of the
duplicate.

How do I modify this macro to delete the ENTIRE row
and to go beyond the A100 row?
The total number of rows can vary in Sheet1 and Sheet2.

http://support.microsoft.com/kb/240077

Here is the code:

Code:

Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
   ' Loop through all records in the second list.
   For iCtr = 1 To iListCount
      ' Do comparison of next record.
      ' To specify a different column, change 1 to the column number.
      If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
         ' If match is true then delete row.
         Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
         ' Increment counter to account for deleted row.
         iCtr = iCtr + 1
      End If
   Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub





Hi All,
The following macro is to delete those rows where their column B are empty.The macro is running extreamly slow as I believe the coding is to search the entire worksheet. Can I have the macro just to search my selection when I hold down the cursor? Thanks in advance.

Code:

Sub deleteBlank()
    Dim Lastrow As Long
    
    Lastrow = Range("b" & Rows.Count).End(xlUp).Row
    
    Range("B2:B" & Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub





Hello - I am tying myself in knots. There are a couple of things that I am trying to do.

1) If I have two lists in excel - I want to identify/highlight common records between the two.
2) I would like to delete records from list 1 if they occur in list 2.

Can anyone help??

Regards,

Cameron


There are plenty of threads like this already, and I've been reading them trying to piece together some code that will work, but having problems. Here is what I have so far:
Code:

Private Sub Worksheet_Deactivate()
Sheets("Records").Unprotect
LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For Ndx = LastRow To 1 Step -1
   If Cells(Ndx, 1 = "Nil") Then
       Rows(Ndx).EntireRow.Delete
   End If
   Next Ndx
Sheets("Records").Protect
End Sub


What I'm trying to do is to delete invalid rows when switching away from the Records worksheet. The only possible invalid row will be the last row with some data autopopulated by another macro. I've a formula in column A starting in cell 2 that checks if the row if invalid, if so a value of "Nil" is assigned, otherwise it's the row # -1.

I'll post back if I manage to solve it before anyone has a chance to weigh in.


I need help with a macro that will look at a smaller list of customer names and VIN numbers and delete all of the duplicates off of another master list with all customers on it. I would like to be able to select the column that i want to sort--for example delete by VIN or name or address..etc. Microsoft has one that is similar but is not easy to use with over 10,000 names. Below is the code they provided. I have two sheets and i am trying to compile them into one sheet with no duplicate VINS and names. Any ideas would be great!
Thanks.


The following sample macro compares one (master) list against another list, and deletes duplicate items in the second list that are also in the master list. The first list is on Sheet1 in the range A1:A10. The second list is on Sheet2 in the range A1:A100. To use the macro, select either sheet, and then run the macro. Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub


I have downloaded a report that is messy and needs to be cleaned up showing only unique records. Instead of manually going through and deleting each duplicate record or row I am wondering if there is a simpler way to accomplish this. In my list I have 10 column headings and column heading labeled ACCT would be the column I would want to verify if the rows below contain the same number, if true, then delete these rows. The list is already sorted by account number.


Hi folks

I know everyone says this but I'm a bit of a newb with Access and the small amount of VBA I understand is self taught.

I have a data entry form that just has one field to enter a loginID. I want it to list on the same form all loginIDs that have been entered so that it's easy to see at a glance which have already been added. I've managed to get it do do this using a list box, is this the best/only way? It needs to be able to update as soon as a new record is saved.

So then, my next problem is how can I delete from that list, so that it comes off the list box and also from the table it's coming from (tblusers)?

I want a delete button that when pressed, deletes which ever loginID is selected in the list box. Any ideas please?

Many thanks
Fran


Hi,

Does anyone know if there is a way to delete duplicate values in list of names. I have a spreadsheet with about 10 000 records but each person is reprasented several times in the list. I would like to simply delete any duplicates, but going through the list deleting row by row is just driving me mad. :-)