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

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*"

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

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

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

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

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