Hi Toppers
have tried it but Im getting Compile error 'End if without block if'
Any suggestions please! Im lost.
Thanks
Ani

"Toppers" wrote:

Hi,

Sort data (PostCode,Surname,Title) and then run this (TEST (data) first!).
It checks ALL three fields and deletes any duplicate rows.


HTH

Sub DeleteDups()

Dim ws1 As Worksheet
Dim lastrow As Long
Dim r As Long

Set ws1 = Worksheets("Sheet1")
With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.And(.Cells(r, "A") = .Cells(r - 1, "A"), .Cells(r, "C") =
.Cells(r - 1, "C"), .Cells(r, "J") = .Cells(r - 1, "J")) Then
.Rows(r).Delete shift:=xlUp
End If
Next r
End With

End Sub


"ani_unicorn" wrote:

Hi John
thanks for the reply. Im not really sure what you mean. If it helps, my
data is 45,000 rows of addresses. The columns run from a-j. I have split
the file into different books for now to make it more manageable.
The column with the post codes in are exact so I can format to recognise
these. Where I have the problem is in the column containing the road number
and name of road. The addresses have been typed in from sheets that often
give more than one house number but the same road name for an individual
surname. The data is then exactly duplicated on columns A C and J but may
vary in details in the other columns. As such:

Title Initial Surname Hse Name Add1 Add2 Add3 Add4 Add4 Add5 Post Code

I know what I want to do but dont know how. I want to auto delete the whole
of the row with the duplicated post code and corresponding surname in. At
the moment I am deleting manually and I am tearing my hair out! I have used
ad filter to delete the unique records but am now stuck!
Hope your Christmas shopping wasnt too much of a nightmare!
Many thanks, and happy holidays
Ani

"John Coleman" wrote:


ani_unicorn wrote:
I have approximately 45,000 entries that are a mix of text and numerics. I
need to set a formula, macro or something that will help me delete 'fuzzy'or
non-identical rows that are duplicated. ie some rows have typos, or other
similar mistakes.
I can use the unique filter and other formulas to find identical cells but
need to know how to find and delete the rows with odd characters in them.
Please help!
Thanks
Ani

It would be relatively easy to write a function that takes as input two
row indices and returns the percentage of columns in which the two rows
agree. You would need to look at your data and decide how much
agreement constitutes a virtual duplicate. Once you have this function,
then a nested loop can be written which deletes rows if they match
agree with a previous row beyond a certain threshhold. I got to do some
last minute christmas shopping now so don't have time to write any
actual code.

Hope that helps

-John Coleman