| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Search
Hello,
I have two tables where I want find the best match according to the description on both tables. The result will be the corresponding code in the second table. Example: first table code description 101 agri 102 single family 103 mobile home 104 vacant land 105 recr Second table Code Description 1000 Agricultural 1002 Single Family Residence 1003 Recreational 1004 Vacant When both tables are matched, the macro should look for the best fit in table 2 and returns the code. The 'Best fit' will depend on words that match (maybe the first 4 to 5 letters) and a count of words. The match with the highest count will define which code should be pulled.... Greatly appreciated! -- EdV |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String Search
On May 21, 6:25*am, LaRana6261
wrote: Hello, I have two tables where I want find the best match according to the description on both tables. The result will be the corresponding code in the second table. Example: first table code * * * * *description 101 * * * * * *agri 102 * * * * * *single family 103 * * * * * *mobile home 104 * * * * * *vacant land 105 * * * * * *recr Second table Code * * *Description 1000 * * *Agricultural 1002 * * Single Family Residence 1003 * * Recreational 1004 * * Vacant When both tables are matched, the macro should look for the best fit in table 2 and returns the code. The 'Best fit' will depend on words that match (maybe the first 4 to 5 letters) and a count of words. The match with the highest count will define which code should be pulled.... Greatly appreciated! -- EdV Like "agri*" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String Search
Thanks, but table 1 will be different each time as I review different data
files. The macro will have to be flexible enough to pick the first 4 to 5 letter in description and search in table 2. Then, return the code in table 2. -- EdV "PY & Associates" wrote: On May 21, 6:25 am, LaRana6261 wrote: Hello, I have two tables where I want find the best match according to the description on both tables. The result will be the corresponding code in the second table. Example: first table code description 101 agri 102 single family 103 mobile home 104 vacant land 105 recr Second table Code Description 1000 Agricultural 1002 Single Family Residence 1003 Recreational 1004 Vacant When both tables are matched, the macro should look for the best fit in table 2 and returns the code. The 'Best fit' will depend on words that match (maybe the first 4 to 5 letters) and a count of words. The match with the highest count will define which code should be pulled.... Greatly appreciated! -- EdV Like "agri*" . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String Search
On May 21, 8:02*am, LaRana6261
wrote: Thanks, but table 1 will be different each time as I review different data files. The macro will have to be flexible enough to pick the first 4 to 5 letter in description and search in table 2. Then, return the code in table 2. -- EdV "PY & Associates" wrote: On May 21, 6:25 am, LaRana6261 wrote: Hello, I have two tables where I want find the best match according to the description on both tables. The result will be the corresponding code in the second table. Example: first table code * * * * *description 101 * * * * * *agri 102 * * * * * *single family 103 * * * * * *mobile home 104 * * * * * *vacant land 105 * * * * * *recr Second table Code * * *Description 1000 * * *Agricultural 1002 * * Single Family Residence 1003 * * Recreational 1004 * * Vacant When both tables are matched, the macro should look for the best fit in table 2 and returns the code. The 'Best fit' will depend on words that match (maybe the first 4 to 5 letters) and a count of words. The match with the highest count will define which code should be pulled.... Greatly appreciated! -- EdV Like "agri*" .- Hide quoted text - - Show quoted text - Perhaps n=inputbox("Number of characters to match") strLike=left(cell, n) & "*" like strLike |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String Search
Hi
Assuming your first table in Sheet1 ColA/B and the second table in Sheet2 ColA/B try the below macro with Sheet1 activated.. Sub Macro() Dim rngData As Range, varFound As Range, lngRow As Long Set rngData = Sheets("Sheet2").Range("A1:B10") For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row If Range("B" & lngRow) < "" Then Set varFound = rngData.Find(Split(Range("B" & lngRow))(0) & "*", _ LookIn:=xlValues, lookat:=xlWhole) If Not varFound Is Nothing Then Range("C" & lngRow) = varFound.Offset(, -1).Value End If End If Next End Sub -- Jacob (MVP - Excel) "LaRana6261" wrote: Hello, I have two tables where I want find the best match according to the description on both tables. The result will be the corresponding code in the second table. Example: first table code description 101 agri 102 single family 103 mobile home 104 vacant land 105 recr Second table Code Description 1000 Agricultural 1002 Single Family Residence 1003 Recreational 1004 Vacant When both tables are matched, the macro should look for the best fit in table 2 and returns the code. The 'Best fit' will depend on words that match (maybe the first 4 to 5 letters) and a count of words. The match with the highest count will define which code should be pulled.... Greatly appreciated! -- EdV |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String Search
I get run error 1004 having to do w/this statement:
"Range("C" & lngRow) = varFound.Offset(, -1).Value".... -- EdV "Jacob Skaria" wrote: Hi Assuming your first table in Sheet1 ColA/B and the second table in Sheet2 ColA/B try the below macro with Sheet1 activated.. Sub Macro() Dim rngData As Range, varFound As Range, lngRow As Long Set rngData = Sheets("Sheet2").Range("A1:B10") For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row If Range("B" & lngRow) < "" Then Set varFound = rngData.Find(Split(Range("B" & lngRow))(0) & "*", _ LookIn:=xlValues, lookat:=xlWhole) If Not varFound Is Nothing Then Range("C" & lngRow) = varFound.Offset(, -1).Value End If End If Next End Sub -- Jacob (MVP - Excel) "LaRana6261" wrote: Hello, I have two tables where I want find the best match according to the description on both tables. The result will be the corresponding code in the second table. Example: first table code description 101 agri 102 single family 103 mobile home 104 vacant land 105 recr Second table Code Description 1000 Agricultural 1002 Single Family Residence 1003 Recreational 1004 Vacant When both tables are matched, the macro should look for the best fit in table 2 and returns the code. The 'Best fit' will depend on words that match (maybe the first 4 to 5 letters) and a count of words. The match with the highest count will define which code should be pulled.... Greatly appreciated! -- EdV |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
String Search
Oh no...This works never mind....thank you thank you!!!
-- EdV "LaRana6261" wrote: I get run error 1004 having to do w/this statement: "Range("C" & lngRow) = varFound.Offset(, -1).Value".... -- EdV "Jacob Skaria" wrote: Hi Assuming your first table in Sheet1 ColA/B and the second table in Sheet2 ColA/B try the below macro with Sheet1 activated.. Sub Macro() Dim rngData As Range, varFound As Range, lngRow As Long Set rngData = Sheets("Sheet2").Range("A1:B10") For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row If Range("B" & lngRow) < "" Then Set varFound = rngData.Find(Split(Range("B" & lngRow))(0) & "*", _ LookIn:=xlValues, lookat:=xlWhole) If Not varFound Is Nothing Then Range("C" & lngRow) = varFound.Offset(, -1).Value End If End If Next End Sub -- Jacob (MVP - Excel) "LaRana6261" wrote: Hello, I have two tables where I want find the best match according to the description on both tables. The result will be the corresponding code in the second table. Example: first table code description 101 agri 102 single family 103 mobile home 104 vacant land 105 recr Second table Code Description 1000 Agricultural 1002 Single Family Residence 1003 Recreational 1004 Vacant When both tables are matched, the macro should look for the best fit in table 2 and returns the code. The 'Best fit' will depend on words that match (maybe the first 4 to 5 letters) and a count of words. The match with the highest count will define which code should be pulled.... Greatly appreciated! -- EdV |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search string to search for wild terms in Access database | Excel Programming | |||
| Wildcard search for string within a string? | Excel Programming | |||
| to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
| search a string withing a string : find / search hangs | Excel Programming | |||
| VBA function : How to search a string in another string? | Excel Programming | |||
Linear Mode
