Deleting Duplicate Rows 06-07-10

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
0
Utf
6/7/2010 8:06:22 PM
excel.programming 6508 articles. 2 followers. Follow

11 Replies
3312 Views

Similar Articles

[PageSpeed] 25

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@discussions.microsoft.com>
wrote:

>Hi,
>I checked out the archives for close to an hour, but I couldn't figure =
out=20
>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=20
>lot of exact duplicates, and I need to delete the rows where those =
duplicates=20
>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=20
>(and the whole row, not just the cell), because it is an exact =
duplicate. =20
>How do I code for this?  Excel 2007.
>Thanks,
>Jbm

0
Gord
6/7/2010 8:30:54 PM
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@gmail.com
"Jbm" <Jbm@discussions.microsoft.com> wrote in message 
news:1FA919E8-69ED-4C39-BCB1-68FBCD6D24F9@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 

0
Don
6/7/2010 8:45:18 PM
=?Utf-8?B?SmJt?= <Jbm@discussions.microsoft.com> wrote in
news:1FA919E8-69ED-4C39-BCB1-68FBCD6D24F9@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
0
Steve
6/7/2010 8:49:04 PM
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
0
Utf
6/7/2010 8:57:54 PM
This assumes Column A is continuously populated from top to bottom of data 
set.  If not, then pick another column to use for finding last row.  You can 
also use the UsedRange property of the sheet if necessary.

Sub NoXDups()

    Dim TestR As Long
    Dim MyStr As String
    
    For TestR = Range("A1").End(xlDown).Row To 1 Step -1
        MyStr = Cells(TestR, "X").Value
        If Range("X:X").Find(what:=MyStr, After:=Range("X1"), _
          LookAt:=xlWhole).Row <> TestR Then
            Rows(TestR & ":" & TestR).Delete shift:=xlUp
        End If
    Next TestR
    
End Sub


"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
0
Utf
6/7/2010 8:58:08 PM
I tried your macro, but it doesn't seem to be working...  Maybe the fact that 
there are headers is screwing it up?  I've been working with your code since 
you posted it, but I can't get it to work correctly (Column A has data in 
every cell until the bottom of my data set).
0
Utf
6/7/2010 9:19:43 PM
Ryguy -- I can't install new software on this machine.

Gord -- it's telling me that it removed 11 duplicates, and 400some unique 
values remain.  Despite this, all the duplicates I can see are still there 
(and I am carefully checking to make sure they are the exact same....  They 
are).
0
Utf
6/7/2010 9:24:53 PM
Well this is deleting things, but not necessarily duplicates, and oftentimes 
cells instead of rows (which means correlated data is getting thrown off).  
Not all the data is in one cell, sorting would be allowed as long as the rows 
of data each stay together.
0
Utf
6/7/2010 9:34:24 PM
      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Jbm" <Jbm@discussions.microsoft.com> wrote in message 
news:BAC9AF70-9077-42FE-B2AA-BCCEB45CC466@microsoft.com...
>I tried your macro, but it doesn't seem to be working...  Maybe the fact 
>that
> there are headers is screwing it up?  I've been working with your code 
> since
> you posted it, but I can't get it to work correctly (Column A has data in
> every cell until the bottom of my data set). 

0
Don
6/7/2010 9:34:44 PM
The headers would not prevent it working unless you had a blank row between 
the headers and the data, or no header in column A.  Before posting, I tested 
this (Excel 2007 Pro) on a block of data with duplicates in column X and it 
worked just fine.  And it accounts for all the conditions that appeared to 
need satisfying.  From your sample data, it seems the duplicates may not be 
listed consecutively, and you specified the first instance should be the one 
left, so re-sorting the data is likely to create problems.

If you've copied this section into a procedure that does other things as 
well, perhaps you could post the whole thing to see if there is some other 
factor causing it to fail.  You don't happen to have any protection on the 
sheet do you?


"Jbm" wrote:

> I tried your macro, but it doesn't seem to be working...  Maybe the fact that 
> there are headers is screwing it up?  I've been working with your code since 
> you posted it, but I can't get it to work correctly (Column A has data in 
> every cell until the bottom of my data set).
0
Utf
6/7/2010 10:15:39 PM
      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Jbm" <Jbm@discussions.microsoft.com> wrote in message 
news:6F1418DF-9E77-4C86-B48D-094DC8132030@microsoft.com...
> Well this is deleting things, but not necessarily duplicates, and 
> oftentimes
> cells instead of rows (which means correlated data is getting thrown off).
> Not all the data is in one cell, sorting would be allowed as long as the 
> rows
> of data each stay together. 

0
Don
6/7/2010 10:23:13 PM
Reply:

Similar Artilces:

How to delete or permantly change logo in personal settings
When I was just learnng how to do publisher I created a logo and Iwant to change it in the permanant settings for the personal information. I cannot delete it or edit it. Also on the other 3 settings for secondary, home etc. it does not alowany logo at all. Anyone know? Locate the following files and delete them: Primary Business - biz1logo.jsp Secondary Business - biz2logo.jsp Other Organization - Orglogo.jsp Home/Family - Perslogo.jsp -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no ...

rows #3
I have a banner that takes up the first two roles and was wondering if there was any way to re-number row 3 to the last row. I would like row 3 to be renamed 1 and so on. You can't re-number the row headers if that's what you mean to do. Just use say, col A to number your rows as desired. Eg enter in A3: 1, in A4: 2. Then select A3:A4, copy down as far as required. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "teddkilroy" wrote: > I have a banner that takes up the first two roles and was wondering if there > was any way to re-number row 3...

Highlighting the active row with special colour
Hi there Is there a macro that highlights the whole active row (not only the active cell) that the cursor is in, in an Excel worksheet? Thanks Raj Highlight it in a particular colour, I meant... that would move along when I would move the cursor onto another row.... "Raj Mazumdar" wrote: > Hi there > > Is there a macro that highlights the whole active row (not only the active > cell) that the cursor is in, in an Excel worksheet? > > Thanks > > Raj Try this '---------------------------------------------------------------- Private Sub Workshe...

2008 SBS questions... 07-04-10
I am trying to get a 2008 sbs running as efficiently as I can & was wondering about a couple of things. 1) I know it wants to be the DHCP server on a network, but what about for wireless clients? I have a wireless sonicwall TZ-170 for wireless laptops to connect to. They really don't need network access, just internet, so can I leave this running, as long as the addresses it hands out aren't in my same network? 2) Although the bought an SBS, the customer is not & probably won't be running exchange for a while. Can I remove exchange all together? I really see no rea...

pulling data 04-27-10
I'm not sure I am explaining this right I want the data from C21 on sheet 1 in a cell on sheet 2 if the A & B column data matches SHEET 1 row 21 A B C D E 4/21/2010 34287 74 3 4 SHEET 2 row 20 A B C D E 4/21/2010 34287 74 try =sumproduct((s1!a2:a22=a2)*(s1!b2:b22=b2)*s1!c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Norm" <Norm@discussions.microsoft.com> wrote in message news:A40C8E08-721F-47BB-A73C-458AC...

80070490 Error: Windows update encountered an unknown error 03-04-10
3 security updates will not complete (now 4). Error Code 80070490 02-Mar-10 I am unable to do these three security updates: KB970238(release date 6/9/09), KB97451(release date 10/13/09), KB954155(release date 10/13/09), & KB975517(release date 10/13/09). The last succesful update was KB9937286 on 2/27/2010 I have a Compaq Presario PC: SR5214X. OS Name Microsoft® Windows Vista™ Home Basic Version 6.0.6002 Service Pack 2 Build 6002 OS Manufacturer Microsoft Corporation System Manufacturer Compaq-Presario System Model GV441AA-ABA SR5214X System Type X86-based PC ...

Delete partial string
Hello! I have a column with strin in cells 26-02-2004 09:27 jhfkfhg fhgfjfg 26-02-2004 10:28 ewqteter rerewewtrre I know delete date/hour group with Replace, but I need inverse. Preserv date/hour group and delete only alfanumeric string. Is possible in Excel? Thanks in advance. an Hi one way (if your data is always structured as below) - add a helper column (lets say B) - insert the following formula in B1 (if column A stores your data): =LEFT(A1,FIND(":",A1)+2) copy down - now you can copy this row and insert it again with 'Paste Special - Values' -- Regards Frank K...

Custom reports 06-13-05
How can I make custom reports in MS CRM? Thanks, M Where can I find Crystal reports? "Mikkhail" wrote: > How can I make custom reports in MS CRM? > Thanks, M "Mikkhail" <Mikkhail@discussions.microsoft.com> wrote in message news:22C1DD57-5461-4924-8CA9-55810497D2D8@microsoft.com... > Where can I find Crystal reports? You need to buy Crystal Reports Developer or Professional Edition v9.2.2. to make custom reports in Microsoft CRM > > "Mikkhail" wrote: > >> How can I make custom reports in MS CRM? >> Thanks, M you may ...

publisher 97 #10
I have a file that was oriniated in Publisher 97. I inadevertently opened and saved it in Publisher 2002 on a different computer(using server). Now I can't access it through 97. Is there a way to do this? Can't save as, as the files of type don't say 97. Maybe they were the same as Publisher 98 files. Worth a try. -- Don Vancouver, USA "Terry" <Terry@discussions.microsoft.com> wrote in message news:D0B451AE-ACF2-449B-8F11-4F0C16297A12@microsoft.com... > I have a file that was oriniated in Publisher 97. I inadevertently opened > and saved it in P...

deleted document
I have deleted an important document and need to retrieve it. The document deleted was on our server and I don't know how to find it and restore it. Contact your IT staff and ask them to restore it from the last backup. But try to find out what share it was on (and folder and filename). It might mean a quicker response. dcalhoun wrote: > > I have deleted an important document and need to retrieve it. The document > deleted was on our server and I don't know how to find it and restore it. -- Dave Peterson Hope your network administrator does regular backups (like da...

Running Office 2001 on Mac OS 10.2, help
This might be a dumb question, but I don't want to screw things up. I was running Mac OS 9.0.4 and I downloaded OS 9.1, 9.1.2, 9.2.2 and then the new 10.2. However, all my software is still in the 9.1 classic. I want to use it all in the new OS 10.2. Will Office 2001 Academic Edition work in OS 10.2? Do I need to uninstall it in OS 9.1 first, then install it is OS 10.2? Or, can I just drag and drop it on the 10.2 desktop or in the applications folder? I have a lot of other expensive softwares in OS 9.1 that I don't want to have to upgrade, but need to be able to use in OS 10.2. Wi...

more than 65,536 rows
Hi Is it possible to have more than 65,653 rows on a worksheet? The data I have does not fit on a normal sheet Harald Harald, Unfortunately, the maximum number of rows on a worksheet is 65,536 rows and cannot be increased. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Harald Bock" <anonymous@discussions.microsoft.com> wrote in message news:D6E9402F-92F4-4649-A72C-BC38BE6C89EB@microsoft.com... > Hi, > > Is it possible to have more than 65,653 rows on a worksheet? The data I have does not...

Mailing List Duplicates
Is there a software program that works with excel to delete duplicate names? Our mailing list is in the thousands. Thanks for any and all help. Gordon habitated@gulftel.com one way? data>advanced filter, copy to another location and unique records only "Gordon" <habitated@gulftel.com> wrote in message news:032201c3796f$ab75f130$a401280a@phx.gbl... > Is there a software program that works with excel to > delete duplicate names? Our mailing list is in the > thousands. Thanks for any and all help. > > Gordon > habitated@gulftel.com ...

Getting back a deleted test frame after saving the changes
My son just deleted a text frame with all his work and started a new text frame and then when asked if he wanted to save his work he clicked yes. Is there any way to retrieve that missing text frame when it won't allow you to undo? On Sat, 27 Aug 2005 16:04:01 +0100, Denise wrote (in article <09056FDE-03FC-4988-8AB0-D1EA0896BFC7@microsoft.com>): > My son just deleted a text frame with all his work and started a new text > frame and then when asked if he wanted to save his work he clicked yes. Is > there any way to retrieve that missing text frame when it won't a...

Deleting Blank lines with VBA
Help I have a macro that runs multiple formulas. However, when the formulas are finished I am left with about 64000 blank lines. I am unable to insert a row on the worksheets as I get a Run-Time Error 1004 (Try to locate the last Nonblank Cell using CTRL-END). Here is a same of some of the formulas I am running ( the macro is very large). ActiveWorkbook.Names.Add Name:="TrunkFormulaI", RefersToR1C1:="=Trunks!R1C27" ActiveWorkbook.Names.Add Name:="TrunkFormulaJ", RefersToR1C1:="=Trunks!R1C28" ActiveWorkbook.Names.Add Name:="TrunkFormulaK&qu...

No Sound 03-26-10
Sorry for not crossposting - I have more problems than I can handle!!! While trying to uninstall Comodo, I had an error and the computer froze. I shut it off and it wouldn't boot - not even in Safe Mode. So I used ERUNT to fix the Registry from a command prompt. Unfortunately, the latest ERUNT file was corrupted - so I had to go back about a week. The computer then booted, but I found many changes in some of my data and settings. Also, I cannot get the sound to work. I again used ERUNT from Windows with the same backup I used from the command prompt. All indications on the ...

Deleting a excel file
Thanks for reading. I'm on a network at work, no signing in needed on the computer. Is there a way I can make a Excel file or folder and have where others can't delete it? I know how to protect it where the file can't be changed, but sometimes someone will delete it. I know this is the Windows 2000 security question but hope to fine the answer here, I'm lost. Thanks Much Bob Talk to your IT staff. Ask them to give you a share or folder on that network that only you (and a trusted co-worker) have write access to. But make...

Check box question 04-23-07
I have a form with a check box used to indicate if a receipt is voided or not. What I'm trying to do is go to a new record after the checkbox is marked. I have the following code in place and it works. Kinda. The problem is that if I then go back and UNCHECK the check box, it sends me to a new record again. Here's the code: Private Sub Check43_AfterUpdate() If Ckeck43 = Yes Then DoCmd.GoToRecord , , acNewRec Thanks for any help! End Sub The following works fine for me. If Me.Check3 = True Then DoCmd.GoToRecord , , acNewRec so try If Ckeck43 = True Then DoCmd.GoToRecor...

ACCESS 2000 is deleting records
Just yesterday I converted an Access 97 database to Access 2000. Have a large problem I need help with. After converting the DB I "split it" in order to use it as a backend, same way it was in Access97. After converting the database to Access2k we began to have a problem with Access2k deleting records. it deleted a total of 4 out of 62 records. the records are not together ( record #289365 then 289048 then 289128 then 289178 then this morning 289405) these record are auto-numbered incerement of one. They were entered by different people so I know its not just an operator probl...

Custom Controls 10-16-04
Hi, I would like to create a custom control .ascx and I'd like to konow how can I put it on a standard CRM form (for example Accounts) Thanks in advance Zsolt There is not a supported way to do this on the standard forms themselves. The closest supported method wuld be to add a new Tab on the left of the page via teh ISV.Confi and have that display your control. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Sat, 16 Oct 2004 01:19:06 -0700, Zsolt Z�mbik <zombikzs@titanium-consulting.net> wrote: Hi, I would ...

Still recieving e-mail for deleted users
We are receiving e-mails for users that were been deleted from our server years ago. Does anyone know how to block these e-mails. We have a SPAM program that allows you to blacklist but it does not stop the messages from coming through. Create ea distribution list containing no members, but which has all the long-gone users' smtp addresses. This is called a blackhole DL. Exchange will accept the mail but silently drop it - no NDRs, no storage. -- hth, SusanV "RB" <pr@epley-pr.com> wrote in message news:574a01c40071$05a154a0$a501280a@phx.gbl... > We are receiving...

Rows and Columns Settings Problem
How do you set rows and columns in a way that when you scrol down/column you can always see a certain row(s)/column(s) -- Message posted from http://www.ExcelForum.com Check out XL Help for "Freeze Panes" In article <JMorgan.1ad5vf@excelforum-nospam.com>, JMorgan <<JMorgan.1ad5vf@excelforum-nospam.com>> wrote: > How do you set rows and columns in a way that when you scroll > down/column you can always see a certain row(s)/column(s). Thank -- Message posted from http://www.ExcelForum.com ...

Minimizing Rows of with similar data
I am using Excel 2007. I have 3 columns A, B, and C such as below: TU10-10 TU10 Tungsten Satin Wedding Band TU10-10.5 TU10 Tungsten Satin Wedding Band TU10-11 TU10 Tungsten Satin Wedding Band TU10-11.5 TU10 Tungsten Satin Wedding Band TU10-12 TU10 Tungsten Satin Wedding Band TU10-12.5 TU10 Tungsten Satin Wedding Band TU10-13 TU10 Tungsten Satin Wedding Band TU10-6 TU10 Tungsten Satin Wedding Band TU10-6.5 TU10 Tungsten Satin Wedding Band TU10-7 TU10 Tungsten Satin Wedding Band TU10-7.5 TU10 Tungsten Satin Wedding Band TU10-8 TU10 Tungsten Satin Wedding Band TU10-8.5 TU10 Tung...

How do I filter rows based upon a column value
I have a spreadsheet that contains multiple agency id's in a column. When generating reports, I would like to filter per agency and display only the rows associated with that agency. Is there a tutorial or sample on how to do this? Hi It sounds like you are looking for Data / Filter / AutoFilter. Have a look here for some basics: http://www.contextures.com/xlautofilter01.html -- Andy. "Jack" <nfr@nospam.com> wrote in message news:eqiU08TVEHA.2988@TK2MSFTNGP10.phx.gbl... > I have a spreadsheet that contains multiple agency id's in a column. When > generati...

List all row source for all forms, reports etc.
Hi I'm doing some work cleaning out old unused forms, reports and queries. I have been going through each report in a database (There are A LOT), determining its row source query, then marking it for deletion. I will eventually end up going through and deleting all the unused queries. As you can imagine this is time consuming, and I was thinkg 'there must be a FASTER way" Does anyone have a suggestion, or link to a pre-built function or model that could assist me? Regards Darragh On Thu, 17 Jan 2008 22:14:18 -0800 (PST), Darragh <darragh.murray@gmail.com> wrote: >Hi...