About Us

[email protected]

Den lÃ¸rdag den 4. februar 2017 kl. 00.14.19 UTC+1 skrev Claus Busch:
Hi,

Am Thu, 2 Feb 2017 00:28:55 -0800 (PST) schrieb :

In Sheet1 I have peoples names and addresses. Names in column A, and addresses in B-column like this:

Mystreet 23, 2.th.
Your Street 5, aptm. 345
Carl Bark Street, 4.th.
Hollowgate 2

and so on.

In sheet 2, Column A I have list of street names like

Carl Barks Street
Hollowgate
Mystreet
Your Street

and so on.

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.

another suggestion:

Sub Test()
Dim LRow As Long, i As Long, j As Long
Dim myRng As Range, c As Range
Dim varData As Variant
Dim myStr As String

With Sheets("Sheet2")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("A1:A" & LRow)
End With

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, "B").End(xlUp).Row
varData = .Range("B1:B" & LRow)
For i = LBound(varData) To UBound(varData)
For j = 1 To Len(varData(i, 1))
If IsNumeric(Mid(varData(i, 1), j, 1)) Then
myStr = Application.Trim(Mid(varData(i, 1), 1, j - 1))
Exit For
End If
Next
Set c = myRng.Find(myStr)
If c Is Nothing Then .Cells(i, 2).Interior.Color = vbRed
Next
End With
End Sub

Regards
Claus B.
--
Windows10
Office 2016

Thank you Claus. That did it.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Comparing columns in sheets	jpreman	Excel Discussion (Misc queries)	6	July 11th 07 03:46 AM
Comparing multiple columns in two sheets	[email protected]	Excel Worksheet Functions	1	March 30th 07 07:12 PM
Comparing columns iin 2 sheets to generate one	CCTD via OfficeKB.com	Excel Worksheet Functions	0	September 26th 05 08:34 PM
comparing columns from different sheets	ali	Excel Programming	0	February 1st 04 10:23 PM
Comparing three columns in 2 sheets	Simon	Excel Programming	3	January 15th 04 04:14 AM

Menu

About Us