compare two columns of partial and/or exact match text

dmasvar

New Member
Joined
Nov 17, 2004
Messages
33
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?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

New Member
Joined
Nov 17, 2004
Messages
33
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

New Member
Joined
Nov 17, 2004
Messages
33
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

New Member
Joined
Nov 17, 2004
Messages
33
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,111
Messages
5,640,156
Members
417,127
Latest member
shakilk

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 MrExcel.com.
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 "mrexcel.com".
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
Top