Suggestion for Macro to Delete Dups based on all fields

[Kay]

I have lists that must be combined via copy and paste. I could paste in
duplicates. The problem is the entire record which might have 15 columns of
data determine whether the record is unique...there is no one unique field.
A record is duplicated if another record is identical in every sense.
the macro suggestions that I have seen so far are for one column's value or
based on specific values in one or more fields.

I would appeciate your help.

 

[Dave Peterson]

I would add a helper column that concatenated all the fields in that row:

=a2&"--"&b2&"--"&c2&.....&"--"&o2

The -- stuff is just to make sure that the concatination of the fields doesn't
match:

For example, if I were to concatenate two fields:
XX and YY to get XXYY
I'd also get that with:
XXY and Y
or
XXYY and (empty)
or lots others!

Then I could use one of those techniques based on a single column.

 

[JLGWhiz]

If you have sorted your file so that you duplicate rows are grouped, then
this will do the job:

Sub delAbsDupes()
Dim Lstcl As Long, lstRw As Long, sh As Worksheet
Dim rng As Range, i As Long, c As Range

Set sh = ActiveSheet
Lstcl = sh.Cells.Find(What:="*", After:=sh.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = sh.Cells.Find(What:="*", After:=sh.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
x = 0
For i = lstRw To 2 Step -1
Set rng = Range("A" & i, sh.Cells(i, Lstcl))
For Each c In rng
If c.Value <> c.Offset(-1, 0).Value Then
x = x + 1
Exit For
End If
Next
If x = 0 Then
Rows(i).Delete
End If
x = 0
Next
End Sub

Without the file being sorted to group the duplicates, it would take a very
complex macro to find duplicate rows randomly through a file with fifteen
columns of data and it would also take considerable time for the macro to
run.

 

[Kay]

Thanks Dave, that sounds like a novel approach. I will see what I can do
with that.

 

[Kay]

Yes thanks, I do not think that it would be to much to ask to have the
end-user sort the field.

I appreciate your help.