Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Comparing two columns in different sheets

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.

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.

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default Comparing two columns in different sheets

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.
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Comparing two columns in different sheets

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.
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Comparing two columns in different sheets

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

--
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.


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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Comparing two columns in different sheets

Auric_,
For some reason in VBA, InStr() doesn't usually work as it does in VB
and so for reliability I specify a criteria...

found = InStr(1, Sheet1.Cells(L0, 2), Sheet2.Cells(L1, 1)) 0
If found Then Exit For

--
Garry

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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default Comparing two columns in different sheets

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.

--
To endure oneself may be the hardest task in the universe.
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Comparing two columns in different sheets

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
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Comparing two columns in different sheets

Oops.., s/b

Have a look at using the Trim() function on B1 so it results text
only with no *extra* 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
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Comparing two columns in different sheets

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
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default Comparing two columns in different sheets

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 11:23 PM
Comparing three columns in 2 sheets Simon Excel Programming 3 January 15th 04 05:14 AM


All times are GMT +1. The time now is 12:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"