This should do the job if the range starts in cell A1:

Sub test()

Dim i As Long
Dim arr
Dim LR As Long
Dim coll As Collection

Set coll = New Collection

LR = Cells(65536, 1).End(xlUp).Row
arr = Range(Cells(1), Cells(LR, 6))

On Error Resume Next
For i = 1 To LR
coll.Add arr(i, 1), arr(i, 5) & Left$(arr(i, 6), 5)
If Err.Number < 0 Then
Cells(i, 2) = 1
Err.Clear
End If
Next i

End Sub


RBS


"Jcraig713" wrote in message
...
I have an excel spreadsheet returned from my database which, among other
data
elements, provides the following critical data:

Household Unique (hlduniq) in Column A
House Number (housenum) in Column E
Streetname (streetname) in Column F

Is it possible to write coding to return records of duplicate addresses?
What I was thinking of doing was writing something to say if house number
is
the same and the first 5 characters of streetname are the same and
household
unique is different, then return the record for resolution.

In the end, in one school building, I have over 10,000 student records to
look through to see if the street number and street name are the same and
the
household ID is different, then I have a duplciate household in my system.
Is there a way I can write code to look at my data for me and return the
records the coding finds fits my criteria so I do not have to look through
10,000 records by hand? I am an extreme novice at this so any assistance
you
can provide to help me learn how to write this type of logic would be most
appreciated. Thanks for reading this post.