Identifying common data between 2 cells

[MRPRMR]

Hello- I am trying to identify common criteria between 2 cells usin
Excel 2007. For instance: I have a series of numbers and sometime
letters in 1 cell (manufactures part number). I want to compare t
another cell that has similar but not exactly the same data.
The info might look like this....(Cell 1) 55758 vs. (Cell 2) AVT55758.

Another example could be (Cell 1) abc55758 vs. (cell 2) AVT55758950. I
both examples, the consective series of numbers "55758" are common
therefore this would be a valid match.
The difference is between how a vendor might list a product. I
reality, it's the same product. I want to be able to either have a macr
or a formula that could match a minimum of 4 consecutive numbers an
give me a result that says it's a good match. If it's 5 cons. numbers
then it's an excellent match. It's a time eating process to manuall
view data and a macro or formula like this would be a great time saver.
Thanks

 

[Nathan Liebke]

Hello- I am trying to identify common criteria between 2 cells using

Excel 2007. For instance: I have a series of numbers and sometimes

letters in 1 cell (manufactures part number). I want to compare to

another cell that has similar but not exactly the same data.

The info might look like this....(Cell 1) 55758 vs. (Cell 2) AVT55758.



Another example could be (Cell 1) abc55758 vs. (cell 2) AVT55758950. In

both examples, the consective series of numbers "55758" are common,

therefore this would be a valid match.

The difference is between how a vendor might list a product. In

reality, it's the same product. I want to be able to either have a macro

or a formula that could match a minimum of 4 consecutive numbers and

give me a result that says it's a good match. If it's 5 cons. numbers,

then it's an excellent match. It's a time eating process to manually

view data and a macro or formula like this would be a great time saver.

Thanks.

Give this a try:

Public Function MATCH_STRENGTH(InputValue As String, CompareValue As String) As Integer

Dim InputStringLength As Integer
Dim i As Integer
Dim MatchValue As String

InputStringLength = Len(InputValue)

' Look for 5 match
For i = 1 To InputStringLength - 5
MatchValue = Mid(InputValue, i, 5)
If InStr(CompareValue, MatchValue) > 0 Then
MATCH_STRENGTH = 5
Exit Function
End If
Next i

' Look for 4 match
For i = 1 To InputStringLength - 4
MatchValue = Mid(InputValue, i, 4)
If InStr(CompareValue, MatchValue) > 0 Then
MATCH_STRENGTH = 4
Exit Function
End If
Next i

MATCH_STRENGTH = 0


End Function


You should then be able to call it in Excel like:

=MATCH_STRENGTH(A1,B1)