Match Text string

[KalleeP]

I am trying to figure out a way to enter in a text string (6 letters in length), and then search column A for exact matches(contains 1000's of 6 letter strings) . The order of the letters within the string is not important. However if the string has duplicate letters, the match must have the same number of duplicate letters (aaabcd =bcdaaa, aabbce<>abbbce). Not quite sure how to tackle this, Vlookup,if statements

Any thoughts?

 

[Damon Ostrander]

Hi KalleeP,

I don't know of any Excel built-in worksheet functions that can easily accomplish this. So here is a VBA user-defined function (Amatch) and macro (FindAmatch) to do it.

To install this code in your workbook go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM), and paste the code into the Code pane.

To use the macro select a cell at the top of the column you want to search. Then run the macro The search will look for the next match in that column. To run the macro go to Tools > Macro > Macros, and run FindAMatch. You can also either assign a keyboard shortcut using this same menu or assign a button to this macro.

Keep Excelling.

Damon



Sub FindAmatch()
'searches column for text string match with characters in any order
'starts search with selected cell
Dim SearchStr As String
Dim LastRow As String
Dim iRow As Long
Dim Answer As Variant

LastRow = Cells(65536, Selection.Column).End(xlUp).Row

SearchStr = Application.InputBox("Enter search string", _
"Find A Match", Type:=2)

If SearchStr = "" Then Exit Sub

For iRow = Selection.Row + 1 To LastRow
If Amatch(SearchStr, Cells(iRow, Selection.Column)) Then
Cells(iRow, Selection.Column).Select
Answer = MsgBox("Match Found" & vbLf & _
"OK = continue search" & vbLf & _
"Cancel = stop search", _
vbInformation + vbOKCancel, _
"Find Match for [" & SearchStr & "]")
If Answer = vbCancel Then Exit For
End If
Next iRow

End Sub

Function Amatch(S1 As String, S2 As String) As Boolean
Dim S2now As String
Dim iCh As Integer
Dim nCh As Integer

Amatch = False
If Len(S1) <> Len(S2) Then Exit Function

S2now = S2

For iCh = 1 To Len(S1)
nCh = InStr(1, S2now, Mid(S1, iCh, 1))
If nCh = 0 Then Exit Function
S2now = Left(S2now, nCh - 1) & Mid(S2now, nCh + 1)
Next iCh
Amatch = True
End Function


Note also that you can use Amatch directly from a worksheet. For example

=Amatch("aaadbcd",A1)

will yield TRUE if cell A1 contains "aaadbcd" or "bcdaaad" or "aaabcdd", etc.

 

[fairwinds]

Hi,

I'm sure Damons suggestion is easier and more efficient, but as formulas are more fun

=MATCH("@@@@@@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A30,MID(C2,1,1),"@",1),MID(C2,2,1),"@",1),MID(C2,3,1),"@",1),MID(C2,4,1),"@",1),MID(C2,5,1),"@",1),MID(C2,6,1),"@",1),0)

Must be entered with Ctrl + shift + enter-

Book1
	A	B	C	D
1
2	ABCDEF		DCABBB
3	AAABCD
4	AABBCC		4
5	ABBBCD
6	DCBAEF
7	ACCDEF
8	CCBACD
9	AAAAAA
10	ABABAB
Sheet1

 

[erik.van.geit]

Fairwinds,

Do I understand this well?
You formula searches for the first cell which contains the same 6 characters.
In your example it's the 4th cell of the range, hence "4" as result.

kind regards,
Erik

 

[fairwinds]

Yes Eric.

MATCH returns the position, in the range, for the first match.

 

[KalleeP]

Thanks all, really appreciate the help, I had "make shift" solution in place, that kinda worked (only about 80% effective). I will give these a try.

Thanks again

Kallee