This is really a shot in the dark, my imagination may just be wild.
I am working on a data validation report that conditionally formats a list report based on mismatches. I am comparing address between 2 different Data sources to identify those needing update.
for example:
Add_1 Add_2
8515 CEDAR PLACE 500 E 96TH ST
My conditional formatting will highlight Add_2 in green because of the mismatch in this case. This all seems to work just fine for the most part.
My issue is when there maybe different naming convention.
for example:
Add_1 Add_2
500 East 96TH ST 500 E 96TH ST
I want to ignore cases like this as you can see its just East instead of E.
My question is, is it possible to compare just the leading #s between 2 variables?
Please keep in mind that addresses have more or less numbers on the Street add e.g 50009 E 96TH ST.
Again, I want to compare just the leading numbers between the 2 address fields.
Thanks for taking your time for this,
R.
Quote from: Zimbo_African_1980 on 27 Nov 2018 11:20:36 PM
This is really a shot in the dark, my imagination may just be wild.
I am working on a data validation report that conditionally formats a list report based on mismatches. I am comparing address between 2 different Data sources to identify those needing update.
for example:
Add_1 Add_2
8515 CEDAR PLACE 500 E 96TH ST
My conditional formatting will highlight Add_2 in green because of the mismatch in this case. This all seems to work just fine for the most part.
My issue is when there maybe different naming convention.
for example:
Add_1 Add_2
500 East 96TH ST 500 E 96TH ST
I want to ignore cases like this as you can see its just East instead of E.
My question is, is it possible to compare just the leading #s between 2 variables?
Please keep in mind that addresses have more or less numbers on the Street add e.g 50009 E 96TH ST.
Again, I want to compare just the leading numbers between the 2 address fields.
Thanks for taking your time for this,
R.
You can use a stubstring function combined with a position function to parse out the bit up to the first blank space. This doesn't do anything to look for numbers (versus characters) but might suit your purpose.
substring ( [address 1], 1, position ( ' ', [address 1] ) )
Thanks Lynn, this actually worked. I was able to validate and correct about 30 % worth of records.
Quote from: Zimbo_African_1980 on 03 Dec 2018 11:03:24 PM
Thanks Lynn, this actually worked. I was able to validate and correct about 30 % worth of records.
Great! Glad I could help.