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

--
In life, they were a motley crew: farmers, lords, cutpurses, priests.
In death, they are united in a singular, benevolent purpose.

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.