Coding Question
 

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.

Coding Question
 

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.