Tidying up addresses database - removing redundant variant records

Good morning/ afternoon

I know that the following could be more tidy. Forgive my transgression in this regards and any assistance you can provide is greatly appreciated.

In short I have about 25,000 entries of addresses to trawl through (again - already did this manually 6 months ago), tidying any redundant variants out - and am looking to save myself a lot of manual trawling

Sample of Excel Data Attached - pre and post-processing:Sample.xls

/\/\/\/\/\


What I am personally hoping for is advice and perhaps snippets of code that could:

a) Firstly determine the 'alphas'

(start pseudo code)

If current record in list is identified as an official record (H),
Identify the closest match to current official record's Street (D) (and Prefix (C)?)
where the Locality (E) is the same,
and assign the same official database value (G) to the same column of the chosen record (G).

(end pseudo code)

b) and... once the 'alphas' are determined: (and optionally with the removal of 'officials' from the picture'):

(start pseudo code)

If current record in list is identified as an 'alpha' record (G)
Identify all records of adequately similar match to current record's Street (D) (and Prefix (C)?)
Where the Locality (E) is the same,
and assign the same official value of the alpha (B) to column (F) of the variant (to facilitate database update)

(end pseudo code)

SOS?

Hi and welcome to the forum

Just a reminder that ALL members here are all volunteers, no-one gets paid for anything (except on the commercial forum), we all contibute our knowledge free of charge, as and when we can.

It could be that nobody has seen this post (I hadnt, until you bumped it), so I would suggest a little more patience, Im sure you will get the answer you want soon

Also, to assist in helping you, it might be better if you included a sample workbook, showing the data you are working with (no sensitive data), what your expected outcome is, and how you arrived atthat.
(your 1st thread is quite a mouthful, so maybe that put off some members)

Thank you kindly for the welcome and also your advice. I 'was' worried that the OP was too wordy - Will try to fix (and include a sample workbook - there is little sensitive information as such)).

I can certainly appreciate that nobody owes me a response. This is certainly not the way I wished it to sound and I apologize to those who took it that way.

/\/\/\/\/\

For the sake of freeing up the original post of unnnecessary background information - I am relocating it here for those who find any part of it useful for gaining a better grasp of the issue.

Background:

Well it seems that I've inherited a bit of a hefty problem - a database with a good 25,000-odd addresses that I need to tidy up.

How did it get so untidy? All users could create new street entries (rather than actually search for them).
The result: Numerous variants of the same address.

Now that I've finally sorted out the how (choice from list - no longer adding redundant records to the list) I still have somewhat of a monster task to tidy these up - a good couple of months work do do it manually.

I cannot help but think that there is a much better way to go about it though and this is where I ask for your advice.

Databases:

To help work on this matter I have combined two databases - one featuring our own data and the other featuring the official list of addresses. I am using the later list to determine an 'official' match amongst the multiple instances of variants of this address and then I am associating the variants with this 'alpha' address.

The intention is to replace the street data of the variants with that of the 'alpha', reducing or eliminating redundancy and very much tidying up the database to make any further manual tidying a lot more managable.

The reason I cannot simply ditch the first for the later is because clients are associated with the former - and the links binding person and address would be lost.

Columns of data:

(Column) - Purpose

(A) Not in Use
(B) Unique database address (integer);
(C) Street Prefix (String);
(D) Street Name (String);
(E) Locality (String);
(F) Replace Column 'A' With this Value if not (Integer);
(G) Address reference for 'official' database - also assigned to 'alpha' record in database (integer);
(H) Identifier of 'Official' Database (So as not to confuse 'official' with 'alpha') (String);

-



/\/\/\/\/\

Data Sample:

Please Login or Register  to view this content.

(side-note, 'Triq' is the local word for Street (we use it prior to the name rather than afterwards, as in the English language))