Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two columns in different sheets
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two columns in different sheets
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing columns in sheets | Excel Discussion (Misc queries) | |||
Comparing multiple columns in two sheets | Excel Worksheet Functions | |||
Comparing columns iin 2 sheets to generate one | Excel Worksheet Functions | |||
comparing columns from different sheets | Excel Programming | |||
Comparing three columns in 2 sheets | Excel Programming |