compare two columns of partial and/or exact match text

[dmasvar]

is there a way of comparing two colunms of text say and drawing out the similiarities, wehther exact match or partial match...

would appreciate any assistance..
eg

one column may have

cat, camp

and 2nd column could have

cake, cow

the output column 3rd column

if i ask to match "ca" it should say there is a match and bring out cat, cake camp


is this possible how do i do it.. sql?expresison builder?

 

[Joe Was]

Sub myFoundLst()
'Sheet module code like: Sheet1
Dim myRng As Range
Dim myMsg$, myTitle$, myDefault$, myVal$, f

myMsg = "Enter a search string:"
myTitle = "Find All!"
myDefault = ""

myVal = InputBox(myMsg, myTitle, myDefault)

Set myRng = ActiveSheet.Range("A1:B33")

With myRng
Set f = .Find(myVal, LookIn:=xlValues, LookAt:=xlPart)

If Not f Is Nothing Then
firstAddress = f.Address
Do
myLst = myLst & f.Value & " at: " & f.Address & vbLf
Set f = .FindNext(f)
Loop While Not f Is Nothing And f.Address <> firstAddress
End If
End With

MsgBox "Found:" & vbLf & myLst
End Sub

 

[dmasvar]

thanks Joe

I'll have a look at it,
but rather than specifying what the criteria should be,

can anyone hlpe me change joe's code to simply draw out all possible matches, whether exact or partial.

 

[dmasvar]

joe that was a seriously hot script!!

can i ask for another favour, i'm studying your script.. man you've got some talent,

but i'm wondering, is it possible to format the underlying sheet for the matches, such that it only shows the matches and filters out everything else..

that way its easier on the eye to see, .. as I will manipulate the text later with the worksheet...

greatful if its possible.

 

[dmasvar]

sorry me again..

aside from filtering out the other non matches of the undlerying worksheet, perhaps more important is can the scdript be changed to make it search for matches and partial matches automatically.. without user entering in a code..

as my list is some 16 000 rows of 2 colunmns of text, each column more than 1 word, eg. if there is 2 word each in 2 columns, there is 4 permutation combination for the computer to search for possilbe partial matches..

the only user text could be simply how many letters to tests for?

it would than go away and test on both 2 words of each column....? and provide some output on the worksheet column c for example?

sorry i know i'm pushing it, just this would be useful little application for everybody else, i've looked high and low and tried expression builder but can't do it.

 

[Joe Was]

Expression builder is for sheet formulas limited to simple cells, so no you cannot use it to build an application for this you need Cod as I have indicated.

My code does look for both whole and part based upon what the user has indicated that they want to look for. The code limits the search to A1:B33.

The code can be modified to flag a row in another column if the string is found then additional code can delete all the rows not containing the flag. This way only the rows that match the search string will be on the sheet.

You may also look into the Filter and AutoFilter both of which will serve your purpose. That is they hide rows that do not contain your data, but unlike the flag method above you can get the hidden rows back [AutoFilter dropdown "ShowAll"], which you cannot do using the delete method!

If you want to do it by code start changing the code to fit your data and your sheet and post the code back when you need to change or add something you are having a problem working out.