compare two columns of partial and/or exact match text


New Member
Nov 17, 2004
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..

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?

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
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
Upvote 0
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.
Upvote 0
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.
Upvote 0
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.
Upvote 0
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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back