jan120253 wrote:

Den torsdag den 2. februar 2017 kl. 12.28.03 UTC+1 skrev
:
Den torsdag den 2. februar 2017 kl. 11.02.14 UTC+1 skrev Auric__:
jan120253 wrote:

In Sheet1 I have peoples names and addresses. Names in column A,
and addresses in B-column like this: [snip]
In sheet 2, Column A I have list of street names like [snip]
What I need to do is compare the street name part of the address
(Sheet 1, column B) with the list of street names (sheet2, column
A) and mark all the adresses where the street name doesn't have a
match in the list of street names. In the above example the list
of adresses contains the name Carl Bark Street, which is
incorrect as the name in ther list is Carl BarkS Street. So it
has to marked. Sio does streets with extra blanks, or street that
do not exist at all.

The challenge for me is that the road names can have none to
several blanks in them, and what follows the road name in the
addresses also have different content.

Is it possible at all to do something like what I want.

Try the VLOOKUP function. Put this in C1 (or any convenient cell
on the row) and copy down:

=VLOOKUP(B1,Sheet2!A:A,1)

(If the data doesn't start at B1, change it appropriately.)

All addresses without matches on Sheet2 will show as #N/A:

Mystreet 23, 2.th. Mystreet
Your Street 5, aptm. 345 Your Street
Carl Bark Street, 4.th. #N/A
Hollowgate 2 Hollowgate

If you want to do this in VBA, you can either use
WorksheetFunction.VLookup, or do it the long way:

Sub bruteForceFinder()
Dim L0, L1, found As Boolean
For L0 = 1 To
Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row found =
False For L1 = 1 To
Sheet2.Cells.SpecialCells(xlCellTypeLastCell).Row If
InStr(1, Sheet1.Cells(L0, 2).Value, Sheet2.Cells(L1,
1).Value) _ Then found = True: Exit For
Next
'Bolds cells on Sheet1 without a match on Sheet2.
'Change this to whatever you want to happen.
Sheet1.Cells(L0, 2).Font.Bold = Not (found)
Next
End Sub

Unfortunately the formula doesn't work when the error is an extra
blank,as it still returns the closest match. I will try the code
later.

Please define what you mean by "an extra blank". Do you mean
something like "Agerlunden 38" (2 spaces between "Agerlunden" and
"38")? Because if so, that's different from a misspelled name. You
could use the FIND worksheet function:

=FIND(" ",B1)

...which returns a number if 2 spaces are found or #VALUE! otherwise,
or another InStr in VBA:

InStr(Sheet1.Cells(L0, 2).Value, " ")

I made a short testrun of the code

In sheet1 I have the following addresses

Aerlunden 17
Agerlunden 38
Ahornvang 10
Ahornvang 14
Ahornvang 6

and in sheet2

Agerlunden
Ahornvang

What I had expected was that the code would have bolded Aerlunden 17
as Aerlunden does not exist in sheet2, but nothing is bolded

Interesting. My code works just fine for me using that data (and the
example data you originally posted), but see also Garry's (GS)
reply. (I've never had that problem, but... shrug. Perhaps wrap the
InStr in a CBool or something. I know I'm relying on a misfeature,
but I'm lazy.)

Note that the code I posted assumes that the addresses are *exactly*
as you specified in your original post: addresses to be checked in
Sheet1 column B, known street names in Sheet2 column A. If any of
those assumptions are wrong, the code will fail.

Personally, I would step through the code and see what each variable
holds at the InStr line, because that is likely where the problem
is.



Okay, here's a formula that... eh... works. (Note that worksheet
formulae aren't my strong point, not by a long shot.) This catches
doubled spaces and non-matching street names:

=IFERROR(FIND(" ",B1),IFERROR(VLOOKUP(B1,Sheet2!A:A,1),""))

If the result is...
- a street name: the address is (in theory) good.
- a number: there's a double space.
- blank: the street isn't on Sheet2.

An inelegant solution, to be sure, but better than nothing, unless a
formula expert takes an interest. You could experiment with the
various text functions, maybe something will give you better
results.

Have a look at using the Trim() function on B1 so it results text only
with no spaces...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion