Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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.

View Answers     

Similar Excel Tutorials

Delete All Rows that Contain a Specific Value in Excel
Quickly find all rows in Excel that contain a certain value and then delete those rows. This is a simple technique ...
Delete Duplicate Values in All Versions of Excel
How to delete duplicate values from a data set in all versions of Excel.  This includes Excel 2003 and earlier and ...
How to Add, Remove, and Rearrange Columns and Rows in Excel
Adding, removing and rearranging columns is as easy as resizing. You just select your columns or rows, right click ...
Prevent Duplicate Values in Excel
I show you how to prevent duplicate values being entered into Excel using Data Validation. Let's say we have a lis ...

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
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
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

Similar Topics







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


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




Hello,

I have records in a database (list b in sample spreadsheet), I'd like to add unique records (list A), while eliminating any records that are in list b already. So this is not a straight forward eliminating duplicates.

The resulting list should be only records from List A that are not found in List B.

Thanks for the help.

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. 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 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'm struggling with a simple task - trying to filter a list of records according to whether one of the colums of the record appears in a list.

Basically I have a spreadsheet with a number of records with the following columns: BP code; BP name; contact name; email address etc.

Then I have created a second list of the BP codes I am interested in and want to filter the records to get to only the ones which have BP code which appears on the second list.

Hope that makes sense. I have attached a cut down spreadsheet with sensitive data removed and would be grateful for any help with this.

Edit: Meant to add I highlighted the records in yellow that I want to end up with - records with BP Code CAQU002 should not be in results because that BP code is not in second list..

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





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.


Ok, have another one for the gurus.

I have a list of names. (Let's say B1:B10) I have four cells picking random names from that list. (Let's say A1:A4) So, I want A1 to pick from all of the names, then A2 to pick from all of them, except I want the name A1 picked to be excluded. So if there were 7 names originally, A2 would only have 6 to choose from, and ect ect for A3 and A4.

Basically I just want to ensure that all four cells pick unique names from the list.

(The word unique just made me think that there might be an excel function for this but I'm going to post the thread anyway...)

EDIT:
Quote:

Alright, kinda figured out, but still want to know if there is an easier way.

This is what I did.

I made a new column beside my column of names. Let's call it B1:B10. In B1 I put
Code:

=randbetween(1,Num in list)


In B2 I put

Code:

=MOD(B1+ INT(RAND() * (Num in list - 1) ), Num in list ) + 1


I know how it works, but I can't seem to make it work for 4(or 5 or 6) picks. Here's where I found the solution.

http://www.ozgrid.com/forum/showthre...=100598&page=1

Found it online. Seems to work ok for 2 picks. Gotta be a better way though.

Ok I figured out the formula, and I don't believe it can be adapted for 4 picks. So what I did was make a column of random numbers up to however many names were in the list. So if 7 names my random nums went from 1 to 7. Then I did an if statement that said "if the first number in this column is not equal to either of my first two picks, then it's the third pick, and if not, then it goes to the second number in the list of random numbers." I realize this is limited, but with enough of them, it'll be nearly impossible to get to the point where it doesn't pick a good number. Ugh it's ugly.


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,

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. :-)


Hello -

I have looked around this site and found some examples of how to write this macro but unfortunately none of them work for me.

I have a list of data (this list is variable) and I want to delete the rows that have the word Revaluation in column L.

How do I do this?

I tried the following but it did not work

Sub Delete()

Dim Lastrow As Long, i As Long
Lastrow = Cells(Rows.Count, "L").End(xlUp).Row
For i = Lastrow To 1 Step -1
If Range("L" & i).Value = Revaluation Then Rows(i).EntireRow.Delete
Next i
End Sub

Any help would be appreciated.


Hello all,

I was wondering if anyone knew of a way to do the reverse of the Delete Duplicate Function in Excel on the Data Tab.

Where the function allows you to select column headers to check for, then finds the records that are duplicates and deletes only the duplicate.

I would like a macro that does the opposite, allows me to keep all of the duplicates and delete the unique records only. Leaving me with both of the records that contained the specified column info ("=RC2&""-""&RC3&""-""&RC6").


Hi,
Could somebody please help me with the below stated ...

I have an excel work Book -
Sheet 1 - Contains 50,000 records of customer details running through Column A to U
Sheet 2 Contains around 1500 records randomly taken from the list in Sheet 1 and updated, running through Column A to U
The headers are same in both the Sheets

Requirement -
Those 1500+ records in Sheet 1 are now duplicates since they are found in Sheet 2. I want to delete those records in Sheet 1 (main database) (in other words delete those 1500 records from 50000). So that I can proceed with the updates of the other records.

The only Unique Column is the CID number which is in Column 2 (in Both Sheets)

May be this can be used to compare and detect duplicates from both the sheets


Please....
Thank you
Regards
Blessy


Hello, I have a large amount of records in excel some of the records contain data on the same person, I need to taille up one of the fields from each of the duplicates and then delete the the duplicate records. Does this sound possible without VBA code anyone got any ideas on how to achieve this???

Thanks in advance. Harry


I ned a macro that will look up a value on one worksheet to another and if it's there, it would delete that row. i.e. I have a list of products that are no longer active and I have the entire list of all products. I want to compare the inactive list to all and if the inactive part is on the list of all, delete that line from the file of all parts. Here's what I've been working with, but it doens't work. On the Delete list, there is one column that contains the part numbers that are to be deleted from the final sheet. But I want the entire row on the final sheet to be deleted. PLEASE HELP!!!! Thank you in advance!


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("Final").Range("A1:A14080").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Delete").Range("A1:A66")
' 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("Final").Cells(iCtr, 8).Value Then
' If match is true then delete row.
Sheets("Final").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


This has been kickin my but all day. I am in excel 2003 i have two lists of names. I need to extract unique records to Column C (Records to be Added).

Column A (Old List) =322 records

Column B (New List) = 475 records

Column C (Names To be Added)=IF(ROWS(C$2:C2)

I have a list of records with ID and Names. I have completely unrelated column which lists the IDs. I want to create a macro which would delete the rows with IDs, present in the other column.

To better explain, I am attaching a sample spreadsheet, which contains a list of records and the yellow column is the one from which values of the ID must be checked and if present in the yellow column, should be deleted.

Kindly let me know if this is possible.

Thanks,
Booo


Hi,

I have the folowing line of code on cell A1

Code:

=COUNTA($A$5:$A$100)


and on sheet2, cell C1
Code:

=COUNTA(Sheet1!$C$5:$C$100)


In both cases, if I add or delete rows in these ranges, the ranges in the formula also change. How can I add or delete rows maintaining the original ranges?

Regads,
Elio Fernandes