Coding Question

[Jcraig713]

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.

 

[RB Smissaert]

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]

I think perhaps my range is not starting the way you think it is... can I
give you more info because the results show is not correct. My spreadsheet
data look like this:

A B C D E F G
H
hlduniq Sclname StuLast Stufirst house# StrNam City Zip
99 BldgA Black John 100 Second Detroit 48330
100 BldgA Smith Jane 133 Main Detroit 48332
100 BldgB Smith John 133 Main Detroit 48332
101 BldgA Doe Jane 1001 First Detroit 48331
102 BldgB Doe John 1001 First Detroit 48332

What I would like the code to do is to return just the two rows (the Doe's
on the bottom) that the house number and the first three digits of the street
name are the same, but the household unique ID differs. In my system, this
is a duplicate address that needs to be amended to one house unique. I just
need a way to not have to manually look through 10,000 records by hand. Can
this be done? I so thank you for the code, but it was not working I think
because I did not show you the rest of the data (range) in the spread sheet.
Can you help?

 

[RB Smissaert]

Not sure now what the exact requirements are I see that the field hlduniq
does in fact not uniquely
identify a record as you have BldgA and BldgB both with the hlduniq 100.
You will have to adapt the posted code a bit. The principle is simple and
that is that the key of the
collection is unique and if you try to add a key that is in the collection
already then an error will be
generated and the item won't be added. The key will have to be made up
(concatenated) with the
parts of the record as you require. Have a go and come back if you get
stuck.

RBS