is it possible to locate pattern in two different columns, where pattern is located randomly in text strings?

[blbat]

Is it possible to locate pattern in text strings in two different columns, return value of third column, where pattern may be in any random location within the text strings, and pattern to match varies from 6 to 10 characters?

So below cell A2 matches cells B2 and B3 (pattern = ZBQ-125) and returns C2 (Fred).
Cell A4 matches B4 and returns C4 (Paul)
Cell A5 matches B7 and returns C7 (Les)
I've tried the formulas in column D, shown below as well...no joy.
any help would be appreciated.

Excel 2007

	A	B	C	D
1	component:	alternate:	Owner:	result:
2	AD/ZBq-125(V)4, Random Access SET	AD/ZBQ-125 SET Random Access	Fred	NOT FOUND
3	ZL/LDC-144(V)4, Alpha	ZBQ-125	Fred	NOT FOUND
4	LZ-1278	AD/LZ-1278	Paul	NOT FOUND
5	some1234-xr	AD/ZBQ-125 SET Random Access	Fred
6	XR/ABC-445	AD/LZ-1278	Joe
7	no entry	1234-xr	Les

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet2​

Worksheet Formulas Cell Formula D2 =IFERROR(LOOKUP(9.99E+307,MATCH($B2,$A$2:$A$100,C2),$A$2:$A$60),"NOT FOUND") D3 =IFERROR(LOOKUP(9.99E+307,FIND($B3,$A$2:$A$100,C3),$A$2:$A$60),"NOT FOUND") D4 =IFERROR(LOOKUP(9.99E+307,SEARCH($B4,$A$2:$A$100,C4),$A$2:$A$60),"NOT FOUND") <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

 

[Rick Rothstein]

You are correct, the text string: AD/ZBQ-125 would indeed meet my criteria, as it appears in both columns.

Okay, now comes the hard part .... since I don't know what the cells can possible contain, how will be able to have the program identify your code from any common (between the two cells) words that might be in the cell? For a made up example, what if the cells contained this...

A2: AD/ZBq-125(V)4, Random Access SET

B2: AE/ZBq-124(V)4, Direct Access Group

At this point the codes are not equal, but the 6-letter word Access appears in both cells... how is the code I write supposed to know the difference between non-code and code for this type situation? I need a stronger "pattern" than six or more of the same characters.

 

[blbat]

Thanks for sticking with me on this...at this point it's almost become one of those "brain teasers" puzzles!
I was really looking for an easy way!!!

I see your point regarding the word "access" appearing in both cells...it meets the criteria of six sequencial characters in both cells.
The possible matches are too many to be useful.

Unfortunately, there isn't a stronger pattern.

I believe I am going to be stuck writing a definition file I can run a check against.

I've already written code for this type of operation, so all I have to due is manually create the definitions.

For my code below, the worksheet SYSDEFS contains the manually entered definitions..in this case an example definition would be: ZBQ-125

Private Sub cmdEqpReplace_Click()
    Dim x As Integer
    Dim y As Integer
    Dim FoundIt As Boolean
    Dim Sheet1Count As Integer
    Dim SysDefsCount As Integer
    
   
    Application.StatusBar = "Hello, " & Application.UserName & "-   Please wait for Macro to complete!!!"
    Application.OnTime Now + TimeSerial(0, 0, 10), "ClearStatusBar"
  
    x = 1    'Sheet1 column c
    
    Sheet1Count = 1
    Do While True
       If IsNull(Worksheets("Sheet1").Cells(Sheet1Count, 2)) Then
           Exit Do
       End If
       If Trim(Worksheets("Sheet1").Cells(Sheet1Count, 2)) = "" Then
           Exit Do
       End If
       Sheet1Count = Sheet1Count + 1
    Loop
    Sheet1Count = Sheet1Count - 1
    If Sheet1Count < 1 Then
        Exit Sub
    End If
    
    SysDefsCount = 1
    Do While True
       If IsNull(Worksheets("SYSDEFS").Cells(SysDefsCount, 1)) Then
           Exit Do
       End If
       If Trim(Worksheets("SYSDEFS").Cells(SysDefsCount, 1)) = "" Then
           Exit Do
       End If
       SysDefsCount = SysDefsCount + 1
    Loop
    SysDefsCount = SysDefsCount - 1
    If SysDefsCount < 1 Then
        Exit Sub
    End If
    
    'MsgBox Sheet1Count
    'MsgBox SysDefsCount
   ' Exit Sub
    
    
    For x = 2 To Sheet1Count
    FoundIt = False
    
        For y = 2 To SysDefsCount
            If Trim(Worksheets("Sheet1").Cells(x, 2)) = Trim(Worksheets("SYSDEFS").Cells(y, 1)) Then
            Worksheets("Sheet1").Cells(x, 7) = Worksheets("SYSDEFS").Cells(y, 2)
            FoundIt = True
          End If
                             
        
        Next y
           
           If FoundIt = False Then
           
           Worksheets("Sheet1").Cells(x, 3) = Worksheets("Sheet1").Cells(x, 3) & " !!Equipment NOT FOUND!!"
           Worksheets("Sheet1").Range("C" & Trim(Str(x))).Interior.Color = RGB(255, 0, 0)
           
           End If
        Next x
        
      For rwIndex = 1 To 350
        For colIndex = 7 To 7
        With Worksheets("Sheet1").Cells(rwIndex, colIndex)
            If .Value = "MY VALUE" Then .Interior.Color = RGB(255, 255, 0)
            'If .Value = "DELETE IF FOUND" Then .Font.Color = RGB(255, 0, 0) Code runs faster with this statement seperate.
        End With
    Next colIndex
Next rwIndex
      For rwIndex = 1 To 350
        For colIndex = 7 To 7
        With Worksheets("Sheet1").Cells(rwIndex, colIndex)
           If .Value = "MY VALUE" Then .Font.Color = RGB(255, 0, 0)
        End With
    Next colIndex
Next rwIndex
 
 
        Worksheets("Sheet1").Activate
            
            Application.StatusBar = "Macro is Complete!!!"
            Application.OnTime Now + TimeSerial(0, 0, 10), "ClearStatusBar"
                      
           Exit Sub
           
    
End Sub

 

[GaryOfah]

I believe that probably there is a way, but you need to be more precise and open to the solutions.
I was dreaming of something similar few years ago...I think...

Included: splitting names at "/" "-" and LEFT, RIGHT...
And many many formulas...

 

[GaryOfah]

Also, probably matching/counting letters one by one or/and two by two and similar solutions. God knows how many processing horse powers does that need with formulas!? :D
Anyway just for a start it wouldn't be easy. No no that is for sure.

But for the 0 start, your question is not absolutely clear to me. Neither to others I believe.

You can also maybe somehow disregard "access", "group"...

 

[GaryOfah]

Also I think this can probably help you at least maybe to narrow needed work or just look at idea:
http://www.mrexcel.com/forum/excel-...ywords-text-string-using-excel-formula-2.html

 

[blbat]

thanks for the link to the other thread GaryOfah...I will look it over when I get home.

 

[aksabari]

Hi,

I have 3 sorted columns like below and need to compare the column values in similar kind of rows & if fuzzy match (or maximum match count if present) then i need to increment the 3rd column value i.e in my example occurrence count increment to 1 (or) else the count should remain in 1.
The reason is to find the occurrences for similar kind of cause/errors for the same script and increment the same occurrence count 1 by1. If the cause is different then no increment.
As pre-requisite, I would keep ready the excel in the below SORTED format.
Below is my table & the result (count increment) should come in my 3rd column based on comparison of exact string comparison (case insensitive) of my 1'st column & fuzzy match of my 2'nd column (error cause column).

Script Name	Error Cause	Same occurrence count
AA	Due to file issue	1
AA	file issue	2
AA	File not found	3
BB	File not found	1
BB	space issue	1
CC	space issue	1
CC	space issue	2
CC	Db2 error	1

<tbody>
</tbody>

<tbody>
</tbody>

In the above example file issue is the common cause (but in different terms) for 1'st script name AA, so the 3rd column value should increment 1 by 1 (1,2,3) based on the fuzzy match of my cause column.
For script name CC, there r 2 occurrences of space issues and 1 occurrence of db2 issue, so accordingly my 3rd column should increment based on my fuzzy comparison.

Please help.