Using fuzzy logic in excel

Hello everyone,

does anyone know of an application or vb code that can be 
used to search in two worksheets (like VLOOKUP) on 
similar matches using fuzzy logic?  For example, if on 
one sheet I have I.B.M and the other IBM or James Smith 
on one and Jim Smith on the other, is there anyway of 
giving confidence codes to the match?

Thanks very much for your help,

James
0
anonymous (74722)
2/16/2005 11:03:07 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
411 Views

Similar Articles

[PageSpeed] 59

AFAIK this does require VBA code.  I wrote an Access utility in VBA
that does exactly this chore: it works by scrubbing punctuation out of
each list, then comparing each "word" ("Ed" and "Begley" and "Jr" are
each words in this context) in each entry in list A to each word in
each entry in list B.  The output is an integer number corresponding to
the number of word matches found.

0
CycleZen (674)
2/16/2005 2:47:16 PM
Hello James-

Based on the nature of your data, it could be possible that VLOOKUP uses 
logic "fuzzy" enough as it is. If the lookup values include many similar 
entries, though, that may not be the case.

Forgive me if you are already aware of this, but although the fx does look 
for an exact match, but it uses the preceding row once it finds an item that 
is "higher" than what it is looking for. The key is that the lookup table 
must be sorted in ascending order based on the first column.

FWIW |:>)
"James" wrote:

> Hello everyone,
> 
> does anyone know of an application or vb code that can be 
> used to search in two worksheets (like VLOOKUP) on 
> similar matches using fuzzy logic?  For example, if on 
> one sheet I have I.B.M and the other IBM or James Smith 
> on one and Jim Smith on the other, is there anyway of 
> giving confidence codes to the match?
> 
> Thanks very much for your help,
> 
> James
> 
0
CyberTaz (411)
2/16/2005 4:39:12 PM
Thanks for your help.

James
>-----Original Message-----
>Hello James-
>
>Based on the nature of your data, it could be possible 
that VLOOKUP uses 
>logic "fuzzy" enough as it is. If the lookup values 
include many similar 
>entries, though, that may not be the case.
>
>Forgive me if you are already aware of this, but 
although the fx does look 
>for an exact match, but it uses the preceding row once 
it finds an item that 
>is "higher" than what it is looking for. The key is that 
the lookup table 
>must be sorted in ascending order based on the first 
column.
>
>FWIW |:>)
>"James" wrote:
>
>> Hello everyone,
>> 
>> does anyone know of an application or vb code that can 
be 
>> used to search in two worksheets (like VLOOKUP) on 
>> similar matches using fuzzy logic?  For example, if on 
>> one sheet I have I.B.M and the other IBM or James 
Smith 
>> on one and Jim Smith on the other, is there anyway of 
>> giving confidence codes to the match?
>> 
>> Thanks very much for your help,
>> 
>> James
>> 
>.
>
0
anonymous (74722)
2/16/2005 4:47:01 PM
Hi Dave,

I don't seem to be a
>-----Original Message-----
>AFAIK this does require VBA code.  I wrote an Access 
utility in VBA
>that does exactly this chore: it works by scrubbing 
punctuation out of
>each list, then comparing each "word" ("Ed" and "Begley" 
and "Jr" are
>each words in this context) in each entry in list A to 
each word in
>each entry in list B.  The output is an integer number 
corresponding to
>the number of word matches found.
>
>.
>
0
anonymous (74722)
2/16/2005 4:47:53 PM
Hi Dave,

I don't seem to be able to read your whole reply.  Are 
you able to share the access utility that you created?

Thanks,

James
>-----Original Message-----
>AFAIK this does require VBA code.  I wrote an Access 
utility in VBA
>that does exactly this chore: it works by scrubbing 
punctuation out of
>each list, then comparing each "word" ("Ed" and "Begley" 
and "Jr" are
>each words in this context) in each entry in list A to 
each word in
>each entry in list B.  The output is an integer number 
corresponding to
>the number of word matches found.
>
>.
>
0
anonymous (74722)
2/16/2005 4:49:02 PM
James:
Let's take this offline: contact me at Cyclezen@yahoo.com.  I'm a bit
proprietary about applications I've written- but in the spirit of good
Netizenship I'll run the application against your data.  If you'd like
me to do this, send lists A and B as text files to me at
CycleZen@yahoo.com.  I'll send the results list back to you as text (or
as zipped text, since the file can be large).

Let me know-
Dave O

0
CycleZen (674)
2/16/2005 5:53:35 PM
Google fudged my email address: it is
cyclezen at yahoo dot com

0
CycleZen (674)
2/16/2005 7:08:44 PM
Reply:

Similar Artilces:

How can I restore an Excel file that was deleted by mistake?
I deleted a file in Excel from the menu and need to recover it. Can I do this? Hi if you don't have a backup copy probably no chance. You may try a file recovery tool (search Google for some free ones) to recover your deleted file -- Regards Frank Kabel Frankfurt, Germany Diane wrote: > I deleted a file in Excel from the menu and need to recover it. Can > I do this? Did you look in the Recycle Bin? HTH Otto "Diane" <Diane @discussions.microsoft.com> wrote in message news:D41D362F-D1CC-4C14-B0B3-DBC994A32D3C@microsoft.com... >I deleted a file in Exce...

Deleting Msgs after using Inbox Repair Tool
Outlook 2000, XP Pro. After using the Inbox Repair Tool I am unable to delete email messages received before using the repair tool. I reviewed the knowledge base, but did not find anything to help. Need help to delete these old emails. Andy <anonymous@discussions.microsoft.com> wrote: > Outlook 2000, XP Pro. After using the Inbox Repair Tool > I am unable to delete email messages received before > using the repair tool. I reviewed the knowledge base, > but did not find anything to help. Need help to delete > these old emails. In this case, if it wer me, I'...

Problem with Excel 2007
Hi Everyone, It is my first time working on Excel 2007. I was working on a project which I started on Excel 2003, I opened another project for reference and when I returned to the first project all sheets disappeared. The only accessible buttons on the toolbar are the Macro buttons. All macros are still there and sheet names in the VBA are still there. Did I loose my work? Thanks to all albertmb;657269 Wrote: > Hi Everyone, It is my first time working on Excel 2007. I was working on a > project which I started on Excel 2003, I opened another project for referen...

How to Build an Excel-Report ?
Hi, I am Martin from Hamburg and wonder how I can create an Excel-Report. I want to create an Bingo-Card Generator in Excel. Thought and Done, but only the functions of generating the bingo-values by randome and deleting field by random depending on the system variables like number of players etc. So, I have build up one Excel-Worksheet with the right values. In each cloumn are the whole values for one bingo-card. Now, I wonder how to print them. But I cannot find any Report function like in Access. May anyone can help me, how do I get my generated values in an printable report format...

Is there an equivalent of .mda files for Excel?
If you develop an Access application, you can hide your source code by converting the .mdb file into an .mda file. Is there an equivalent for Excel applications? Thanks ..xla, Then Tools | Addins..., to add the file. ..xla is the format for Excel add-in. However, it does not necessarily hide your code in VBA, if that was what you want. Whether it is .xls or .xla, you can only "hide" VBA code by password-protecting the VBA project, which is a weak protection. If you really want to hide your VBA code from viewing by others, the better approach would be to move most of your...

How do I make a Levy Jennings chart in Excel 2003?
How do I use Excel 2003 to prepare Levy Jennings charts for laboratory daily QC data? Hi, Jon Peltier shows you how to add your mean and standard deviation lines here. http://peltiertech.com/Excel/Charts/RunChtLines.html HTH Martin "RayneLily" <RayneLily@discussions.microsoft.com> wrote in message news:ADFC70DF-2277-4430-BDE1-7E41AE2836FF@microsoft.com... > How do I use Excel 2003 to prepare Levy Jennings charts for laboratory > daily > QC data? Try http://peltiertech.com/Excel/Charts/RunChtLines.html .... and isn't it Levey-Jennings ? -- David Biddu...

How do you insert a scale break on the y-axis in Excel?
Is it possible? Hi, Have a look at these examples http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy lakechatei wrote: > Is it possible? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Excel 5.0 #2
I need some help. I have been a faithful user of version 5.0 and I missed my opportunity to upgrade easily. Is there anyone who would be willing to part with an intermediate version so I can make the jump. I understand I need at least an Excel 97. I believe 97 can convert 5.0 and 03 can convert 97. Hi Exc�l 97 - Excel 2003 all have the same file format 8and all can read Excel 5.0 files). So just check ebay and get on of the newer versions (Excel 2000 / Excel 2002) -- Regards Frank Kabel Frankfurt, Germany "Tim in Idaho" <Tim in Idaho@discussions.microsoft.com> schri...

How do I set up a macro to turn on Windows in Taskbar in Excel 200
I have a shared workbook in Excel 2000 that is automatically turning off Windows in Taskbar whenver it is opened and I can't see why. Does anyone out there know a macro that will automatically turn on the Windows in Taskbar option in Excel when the workbook is opened? Thanks David Lee David Add the following line code to an Auto_Open or Workbook_Open event: Application.ShowWindowsInTaskbar = True *pretty sure that was in Office 2000, I am in 2003 so I wasn't able to check... -- Charles www.officezealot.com "David Lee - NZ" <David Lee - NZ@discussions.micros...

Message Rules using <or>
Hello, How do I create a rule that deals with messages sent from certain people OR (rather than AND) sent to a specific one of my addresses? Bob Bob Brannon <bbran@nowhere.com> wrote: > How do I create a rule that deals with messages sent from certain > people OR (rather than AND) sent to a specific one of my addresses? Two rules. -- Brian Tillman ...

excel comment issue
Hi, I'm having a problem viewing comments in all of my Excel 2000 worksheets. Everytime I mouse over the data cell with the comment indicator it comes up black. The only way I can view the comment is selecting edit comment. Any suggestions? Hi Jerry, Searching the archives http://www.mvps.org/dmcritchie/excel/xlnews.htm or directly to: http://groups.google.com/advanced_group_search?q=group:*Excel* is always a good place to start. Not too surprising to find a recent posting <grin> http://google.com/groups?threadm=etAhFlkTEHA.1508%40TK2MSFTNGP11.phx.gbl but if you ...

Error when migrating data from csv file to crm 3 using scribe
I have been trying to migrate data from a .csv file into MS CRM 3 using scribe. When I test the job after doing my connecting and datalinks it executes properly, however, when I run the job it only does on average 12 successful inserts and 11 failed inserts then gives me an error. The error is :"fatal error. Execution terminated - failuere fetching source rows" when I click on the error info button I get a database error " Error 1005. [22018] SQL call failed. Invalid character value" the details of the message :"source[DataDirect][ODBC Driver]" Could someone...

Line graphs in Excel 2007
I am trying to plot weekly sales in a given year. Eventually in a year, there will be 52 weeks of sales labeled date,this yr sales,last yr. sales. I am using a line graph. Everything is working fine except: I would like excel to automatically update my chart for each week that I have sales data for. In the near fuure,when I enter data for the months of Oct,Nov,Dec and the last part of Sept., I would like Excel to automatically update my chart. I have tried to in my data table, put all the weeks of the year in the table and as I get sales data, enter them into the table. The chart will upd...

iif statement using time
I would like to create an iif statement to peform this: I have a form that has a button employees click to login. When they click this button, the time appears in a textbox ([Login]). I would like for another textbox ([Report]) to show whether or not the employee was late. The new textbox ([Report]) should say the words "Late" or "OK" depending on whether or not they logged in before 8:05 AM. Thanks. If it is JUST the time in the LOGIN then you should be able to use =IIF([Login]<=#08:05:00 AM#,"OK","Late") John Spencer Ac...

Excel opens with no document -
Where is the default document for excel when it is launched? I am looking for the normal.dot of the excel world. Symptom: When I open an excel document it launches excel.exe to a blank grey screen. When I open a new Excel document and close it the document I opened is maximized at that point. I was thinking about recreating or pointing to the default excel document if it exists. First try the usual tweaks. Reboot your system. Start Excel with Start - Run - type: excel /unregserver, then excel.exe /regserver. It will start, put in all factory default registry settings, then end. Now ...

Format Inserted Excel Table
I have inserted an Excel spreadsheet as a table into a Word 2007 document (saved as .docx) using the Insert/Table menu. I would like to remove the gridlines. How does one do this? In Excel, there is an option to not show these; there is also the possibility of formatting the cell borders to be white. But neither of these seems to work. I cannot locate an option to turn off gridlines. And when I try to set a cell border color to white, it returns to automatic. Thanks. --ron On Thu, 14 Jan 2010 09:08:16 -0500, Ron Rosenfeld <ronrosenfeld@nospam.org> wrote: >I h...

Excel clicking selects multiple cells instead of a single cell
Excel clicking selects multiple cells instead of a single cell try pressing the F8 key it allows you to select multiple cells and may have been hit by accident "texmaam" wrote: > Excel clicking selects multiple cells instead of a single cell If you're using xl2007, try changing the zoom factor. texmaam wrote: > > Excel clicking selects multiple cells instead of a single cell -- Dave Peterson ...

Outlook address from excel
My titles and names are all on one linein excel. When I import to outlook the Mr and Mrs comes out Mr. Mrs. and. How do I map this Are Mr and Mrs in one field? If so, you'll want to map that field as the title field. If not, you need to merge them into one field. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIB...

Excel Date conversions
How do I set up a column to automatically convert numbers (example - 01012003) into a date format (example - 01/01/2003). I tried doing this by format, cells, date - then entered the number and the date came up different. You have to either use a date format recognized by Excel or use a helper cell/column to extract and concatenate the string to appear as a date: =LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4). -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Gerri" <gpalombi@ci.concord.ca.us> wrote in message news:2672...

open excel file in word
I have an application that use’s VBA for Word that opens an Excel file in Word and then cuts & pastes from the Excel data to a Word template. Works fine in Office 2000. In Office 2003 it works fine on one computer but not on another. One the that works I get a msgbox asking – This file needs to be opened by the Microsoft Excel Worksheet text Converter – when I choose ‘Yes’ the file is converted and things work. On the other computer I get a window ‘File Conversion’. Select the encoding that makes your document readable. Of chooses available the Excel file is converted to an unreadable j...

smart tags in Excel?
Where I'm currently working smart tags would be a good thing. I do most of my work in Excel. Problem is I get tags on the 1st sheet but not the 2nd work sheet. I've tried all the usual things to turn them on with no luck. Can anyone help. ...

My CD won't let me save my Excel workbook on them?
I have created Excel workbooks but when they are saved under excel workbook and I put in or have already have in a CD for saving on, My Excel says that my computer can not format this kind of disc. I have had these discs before and have saved on them many times. I recently bought CD-RW so that I could erase and use again. My computer won't let me. I am sooo frustrated. I have been up late for 3 wks. trying to figure this thing out. Why when I put in a CD of ANY KIND, does it automatically go to Media Player and fill up my cd before I can even save anything on them? Whe...

Want click "+" like in Explorer and have Excel show hidden list, click "-" and hide list
Hi all, I have seen this before and have searched the web but can't find it. Probably because I don't know how to describe it very well. You know how MS Explorer has a "+" beside the "C" drive, you can click on the + and it reveals the folders and folder names under the +. The "+" becomes a "-". You can click on the "-" and explorer hides the list again. That's all I want.... Does anybody know how to do it? I have Office 2002, Excel 10.6501. Much appreciated... For the help. You can get those outlining symbols in a couple...

Controlling Excel Automatic Calculation???
Hi I=92m trying to make a simple routine in excel VB so, when a command button is pressed, the whole workbook is calculated (I have automatic calculation set to manual). I also want to display a warning message to the end user when input data is changed but the =93calculate=94 button has not been pressed. Can anyone point me in the right direction? Any help would be very much appreciated. It is never a good idea to set calculation to manual. nevertheless, this is a normal setting. Put the following code in the Command Button Click function With Application .Calculation = xlAu...

Using Double variable with a C++ dll
Hi, I am using a dll written in C++. One of the declarations is : (In a module) Public Declare Function S52_moveView Lib "libS52.dll" _ (ByVal horizontal As Double, ByVal vertical As Double) As Long (In the code of a form) Select Case KeyCode Case vbKeyUp S52_moveView 0#, 1# Case vbKeyDown S52_moveView 0#, -1# End Select When passing a negative value (with vbKeyDown), the program jams, with an overflow error, or with a division by zero error. I have no idea why this occcurs. Thank you for reading me, an...