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

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

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 

=?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

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

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

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

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

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.

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

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

      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.