Deleting Duplicate Rows
in [Programming]
|
Prev: Macro if criteria
Next: Floating text
From: Jbm on 7 Jun 2010 16:06 Hi, I checked out the archives for close to an hour, but I couldn't figure out how to change the codes given there to suit my needs. I have a large set of data, from about A1 to X441. In column X, there are a lot of exact duplicates, and I need to delete the rows where those duplicates are (but still leaving the first instance of the duplicate). For example: John Smith Oxford St. John Johnson Oxford St. John Johnson Rubble St. John Smith Oxford St. All of those have things in common, but I only want to delete the final row (and the whole row, not just the cell), because it is an exact duplicate. How do I code for this? Excel 2007. Thanks, Jbm
From: Gord Dibben on 7 Jun 2010 16:30 Select column X 2003 Data>Filter>Advanced Filter>Uniques only. Copy to another place. 2007 Data>Remove Duplicates. Unselect all. Select only column X and remove. Gord Dibben MS Excel MVP On Mon, 7 Jun 2010 13:06:22 -0700, Jbm <Jbm(a)discussions.microsoft.com> wrote: >Hi, >I checked out the archives for close to an hour, but I couldn't figure out >how to change the codes given there to suit my needs. >I have a large set of data, from about A1 to X441. In column X, there are a >lot of exact duplicates, and I need to delete the rows where those duplicates >are (but still leaving the first instance of the duplicate). For example: > >John Smith Oxford St. >John Johnson Oxford St. >John Johnson Rubble St. >John Smith Oxford St. > >All of those have things in common, but I only want to delete the final row >(and the whole row, not just the cell), because it is an exact duplicate. >How do I code for this? Excel 2007. >Thanks, >Jbm
From: Don Guillett on 7 Jun 2010 16:45 Another way that does not copy elsewhere IF? sorting is allowed. Assumes all text in ONE cell?? '== Option Explicit Sub SortAndDeleteDuplicatesSAS() Dim mc As Long Dim i As Long mc = 1 'column A Columns(mc).Sort Key1:=Cells(1, mc), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False,Orientation:=xlTopToBottom For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i - 1, mc) = Cells(i, mc) Then Rows(i).Delete Next i End Sub '==== -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Jbm" <Jbm(a)discussions.microsoft.com> wrote in message news:1FA919E8-69ED-4C39-BCB1-68FBCD6D24F9(a)microsoft.com... > Hi, > I checked out the archives for close to an hour, but I couldn't figure out > how to change the codes given there to suit my needs. > I have a large set of data, from about A1 to X441. In column X, there are > a > lot of exact duplicates, and I need to delete the rows where those > duplicates > are (but still leaving the first instance of the duplicate). For example: > > John Smith Oxford St. > John Johnson Oxford St. > John Johnson Rubble St. > John Smith Oxford St. > > All of those have things in common, but I only want to delete the final > row > (and the whole row, not just the cell), because it is an exact duplicate. > How do I code for this? Excel 2007. > Thanks, > Jbm
From: Steve on 7 Jun 2010 16:49 =?Utf-8?B?SmJt?= <Jbm(a)discussions.microsoft.com> wrote in news:1FA919E8-69ED-4C39-BCB1-68FBCD6D24F9(a)microsoft.com: > Hi, > I checked out the archives for close to an hour, but I couldn't figure > out how to change the codes given there to suit my needs. > I have a large set of data, from about A1 to X441. In column X, there > are a lot of exact duplicates, and I need to delete the rows where > those duplicates are (but still leaving the first instance of the > duplicate). For example: > > John Smith Oxford St. > John Johnson Oxford St. > John Johnson Rubble St. > John Smith Oxford St. > > All of those have things in common, but I only want to delete the > final row (and the whole row, not just the cell), because it is an > exact duplicate. How do I code for this? Excel 2007. > Thanks, > Jbm Make a backup first just in case the results are not what you expect. Excel 2007 select the whole sheet ctrl+a, goto data>data tools>remove duplicates, in the dialogue box click unselect all and then select row X click ok this will remove the whole row A-X where X is a duplicate. Regards Steve
From: ryguy7272 on 7 Jun 2010 16:57
Well, those are not all duplicates, so what is the logic? John Smith Oxford St. = John Smith Oxford St. However, John Smith Oxford St. <> John Johnson Oxford St. Take a look at this: http://www.rondebruin.nl/easyfilter.htm Maybe you will have to run through the data a couple times, but that should do what you want. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jbm" wrote: > Hi, > I checked out the archives for close to an hour, but I couldn't figure out > how to change the codes given there to suit my needs. > I have a large set of data, from about A1 to X441. In column X, there are a > lot of exact duplicates, and I need to delete the rows where those duplicates > are (but still leaving the first instance of the duplicate). For example: > > John Smith Oxford St. > John Johnson Oxford St. > John Johnson Rubble St. > John Smith Oxford St. > > All of those have things in common, but I only want to delete the final row > (and the whole row, not just the cell), because it is an exact duplicate. > How do I code for this? Excel 2007. > Thanks, > Jbm |