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

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

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

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

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

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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

Hi
thanks Ken but Im still getting the same message after lining the code up
Cheers
Ani

"Ken Hudson" wrote:

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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

Please copy and paste your code here for review.
--
Ken Hudson


"ani_unicorn" wrote:

Hi
thanks Ken but Im still getting the same message after lining the code up
Cheers
Ani

"Ken Hudson" wrote:

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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

Hi Ken

Here is the code:

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
Thanks
Ani

"Ken Hudson" wrote:

Please copy and paste your code here for review.
--
Ken Hudson


"ani_unicorn" wrote:

Hi
thanks Ken but Im still getting the same message after lining the code up
Cheers
Ani

"Ken Hudson" wrote:

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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

Hi Ani,
Your code still isn't lined up correctly. Please try this out.

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
--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken

Here is the code:

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
Thanks
Ani

"Ken Hudson" wrote:

Please copy and paste your code here for review.
--
Ken Hudson


"ani_unicorn" wrote:

Hi
thanks Ken but Im still getting the same message after lining the code up
Cheers
Ani

"Ken Hudson" wrote:

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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

Hi Ken
the code isnt working. Im not sure why.
Thanks
Ani

"Ken Hudson" wrote:

Hi Ani,
Your code still isn't lined up correctly. Please try this out.

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
--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken

Here is the code:

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
Thanks
Ani

"Ken Hudson" wrote:

Please copy and paste your code here for review.
--
Ken Hudson


"ani_unicorn" wrote:

Hi
thanks Ken but Im still getting the same message after lining the code up
Cheers
Ani

"Ken Hudson" wrote:

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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

Hi,
E-mail me at and I'll send the code. I
am sure the problem is simply aligning the code as Ken has explained.

Thanks Ken for trying to sort this out.



"ani_unicorn" wrote:

Hi Ken
the code isnt working. Im not sure why.
Thanks
Ani

"Ken Hudson" wrote:

Hi Ani,
Your code still isn't lined up correctly. Please try this out.

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
--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken

Here is the code:

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
Thanks
Ani

"Ken Hudson" wrote:

Please copy and paste your code here for review.
--
Ken Hudson


"ani_unicorn" wrote:

Hi
thanks Ken but Im still getting the same message after lining the code up
Cheers
Ani

"Ken Hudson" wrote:

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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

These postings continue to put in line breaks where we don't want them. Let's
try this version.

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

--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken
the code isnt working. Im not sure why.
Thanks
Ani

"Ken Hudson" wrote:

Hi Ani,
Your code still isn't lined up correctly. Please try this out.

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
--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken

Here is the code:

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
Thanks
Ani

"Ken Hudson" wrote:

Please copy and paste your code here for review.
--
Ken Hudson


"ani_unicorn" wrote:

Hi
thanks Ken but Im still getting the same message after lining the code up
Cheers
Ani

"Ken Hudson" wrote:

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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

Hi Ken

I get a Runtime '9' : Subscript out of range error message.

It highlights the "Set ws1 = Worksheets("Sheet1") in Yellow when I press
debug.

I would appreciate any suggestions?


"Ken Hudson" wrote:

These postings continue to put in line breaks where we don't want them. Let's
try this version.

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

--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken
the code isnt working. Im not sure why.
Thanks
Ani

"Ken Hudson" wrote:

Hi Ani,
Your code still isn't lined up correctly. Please try this out.

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
--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken

Here is the code:

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
Thanks
Ani

"Ken Hudson" wrote:

Please copy and paste your code here for review.
--
Ken Hudson


"ani_unicorn" wrote:

Hi
thanks Ken but Im still getting the same message after lining the code up
Cheers
Ani

"Ken Hudson" wrote:

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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

Well, we're getting closer!
In the code, Toppers assumed that the name of your worksheet was "Sheet1."
I assume that you have a different name. You can either rename your
worksheet to "Sheet1" or change the worksheet name in the code to whatever
name is on your sheet.
--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken

I get a Runtime '9' : Subscript out of range error message.

It highlights the "Set ws1 = Worksheets("Sheet1") in Yellow when I press
debug.

I would appreciate any suggestions?


"Ken Hudson" wrote:

These postings continue to put in line breaks where we don't want them. Let's
try this version.

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

--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken
the code isnt working. Im not sure why.
Thanks
Ani

"Ken Hudson" wrote:

Hi Ani,
Your code still isn't lined up correctly. Please try this out.

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
--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken

Here is the code:

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
Thanks
Ani

"Ken Hudson" wrote:

Please copy and paste your code here for review.
--
Ken Hudson


"ani_unicorn" wrote:

Hi
thanks Ken but Im still getting the same message after lining the code up
Cheers
Ani

"Ken Hudson" wrote:

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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

Ani,

I believe that happens because you don't have a sheet named "Sheet1" on your
worksheet. Do you? If not, insert the name of the sheet to be worked on where
Sheet1 appears.

HTH

"ani_unicorn" wrote:

Hi Ken

I get a Runtime '9' : Subscript out of range error message.

It highlights the "Set ws1 = Worksheets("Sheet1") in Yellow when I press
debug.

I would appreciate any suggestions?


"Ken Hudson" wrote:

These postings continue to put in line breaks where we don't want them. Let's
try this version.

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

--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken
the code isnt working. Im not sure why.
Thanks
Ani

"Ken Hudson" wrote:

Hi Ani,
Your code still isn't lined up correctly. Please try this out.

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
--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken

Here is the code:

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
Thanks
Ani

"Ken Hudson" wrote:

Please copy and paste your code here for review.
--
Ken Hudson


"ani_unicorn" wrote:

Hi
thanks Ken but Im still getting the same message after lining the code up
Cheers
Ani

"Ken Hudson" wrote:

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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

Thanks chaps.
Much appreciated! Think its working now, more due to your diligence than my
know how!
Cheers
Ani

"Ken Hudson" wrote:

Well, we're getting closer!
In the code, Toppers assumed that the name of your worksheet was "Sheet1."
I assume that you have a different name. You can either rename your
worksheet to "Sheet1" or change the worksheet name in the code to whatever
name is on your sheet.
--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken

I get a Runtime '9' : Subscript out of range error message.

It highlights the "Set ws1 = Worksheets("Sheet1") in Yellow when I press
debug.

I would appreciate any suggestions?


"Ken Hudson" wrote:

These postings continue to put in line breaks where we don't want them. Let's
try this version.

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

--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken
the code isnt working. Im not sure why.
Thanks
Ani

"Ken Hudson" wrote:

Hi Ani,
Your code still isn't lined up correctly. Please try this out.

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
--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken

Here is the code:

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
Thanks
Ani

"Ken Hudson" wrote:

Please copy and paste your code here for review.
--
Ken Hudson


"ani_unicorn" wrote:

Hi
thanks Ken but Im still getting the same message after lining the code up
Cheers
Ani

"Ken Hudson" wrote:

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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

Fantastic result!
Thanks chaps. You have saved me hours and hours of sitting in front of the
VDU manually deleting rows. I might be able to get this finished in time to
cook chrimbo dinner now!
Just as an example the code just got rid of 600 or so duplicates in a 3,000
row sheet.
Brilliant.
Merry Christmas and a Happy New Year.
Cheers
Ani

"Ken Hudson" wrote:

Well, we're getting closer!
In the code, Toppers assumed that the name of your worksheet was "Sheet1."
I assume that you have a different name. You can either rename your
worksheet to "Sheet1" or change the worksheet name in the code to whatever
name is on your sheet.
--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken

I get a Runtime '9' : Subscript out of range error message.

It highlights the "Set ws1 = Worksheets("Sheet1") in Yellow when I press
debug.

I would appreciate any suggestions?


"Ken Hudson" wrote:

These postings continue to put in line breaks where we don't want them. Let's
try this version.

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

--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken
the code isnt working. Im not sure why.
Thanks
Ani

"Ken Hudson" wrote:

Hi Ani,
Your code still isn't lined up correctly. Please try this out.

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
--
Ken Hudson


"ani_unicorn" wrote:

Hi Ken

Here is the code:

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
Thanks
Ani

"Ken Hudson" wrote:

Please copy and paste your code here for review.
--
Ken Hudson


"ani_unicorn" wrote:

Hi
thanks Ken but Im still getting the same message after lining the code up
Cheers
Ani

"Ken Hudson" wrote:

The row of code the begins with "If..." and the row under it that begins with
".Cells..." need to be one line of code. They are broken in two here, but
need to be one row.
--
Ken Hudson


"ani_unicorn" wrote:

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