String Search
in [Programming]
|
From: LaRana6261 on 20 May 2010 18:25 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
From: PY & Associates on 20 May 2010 18:45 On May 21, 6:25 am, LaRana6261 <LaRana6...(a)discussions.microsoft.com> 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*"
From: LaRana6261 on 20 May 2010 20:02 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 <LaRana6...(a)discussions.microsoft.com> > 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*" > . >
From: PY & Associates on 20 May 2010 21:02 On May 21, 8:02 am, LaRana6261 <LaRana6...(a)discussions.microsoft.com> 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 <LaRana6...(a)discussions.microsoft.com> > > 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
From: Jacob Skaria on 21 May 2010 00:44
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 |