Closest match

[Jon Jagd]

Hi,

I need a function or macro that helps me in matching manufacturer names from incoming data files with all the values in an internal standard list with valid manufacturer names. After the matching it gives me the internal standard value that comes closest to the incoming value. As an example all the incoming values: Abbott Labs, ABBOTT and Abbott Laboratories A/S must be translated to the internal standard value ABBBOTT LABS. I'm envisaging a function similar to this:

=CLOSESTMATCHES(IncomingValue, standardlist)

Ex. I write the following function in cell C3:
=CLOSESTMATCHES(A2;B2:B8)

When the function finds a value in the standardlist that looks like the incoming value it writes the internal standard value in cell C3. If it doesn't find anything it writes f.ex NEW and if there are several matches it writes all the values, either separated by semicolon or in the following cells in the same row.

I don't know if there is a native function that will meet my requirements and it's not that I don't know how to write a custom function. My problem is that I don't know how approach the problem of comparing and matching the incoming values with standard values. I've been considdering the function:
SEARCH(find_text,within_text,start_num)
but I don't feel that it meets my requirements.

Thanks.

Jon

 

[Jon Jagd]

It looks like I'm getting there. The code has been there all the time so I don't really know what has been the problem. Anyway, now I'm getting some results which is great Impressive little program I must say. Thanks a lot.

Jon

 

[al_b_cnu]

Hi,

For posterity, an example of the use of FuzzyVLookup & FuzzyPercent (which is called by FuzzyVLookup & FuzzyHLookup).

In this example, IndexNum has been set to 0 to return the row number, and NFPercent has been set to 50%.

It is generally best to set NFPercent to as high a value as possible (But between 0 and 1)

HTH

Alan

FuzzyExample1.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1				%MatchAlgorithm					Rank
2	Row	LookupTable	LookupValue	1	2	3		Algorithm	1	2	3	4
3	1	GeorgeBush	GeorgeWBush	53.85%	54.17%	54.05%		1	2	3	1	6
4	2	georgewbush		100.00%	16.67%	45.95%		2	4	1	5	#N/A
5	3	George.W.Bush		84.62%	41.67%	56.76%		3	3	1	#N/A	#N/A
6	4	Bush,Georgew		23.08%	58.33%	45.95%
7	5	gorgewbush		15.38%	50.00%	37.84%
8	6	georgeClooney		53.85%	37.50%	43.24%
9	7	tonyblair		0.00%	4.17%	2.70%
10	8	bushbaby		0.00%	12.50%	8.11%
Sheet1

 

[earlyd]

Another approach is one that we took with a customer service application many years ago - that was to create an alias table for typical names for the same company - you could do this in a lookup table and do your match that way; i.e. if it finds it anywhere on the same row. You could structure it to work with a wildcard character. This only works when you have an idea ahead of time who the names will be but don't know how people will enter / send them time after time.

 

[Matt111]

Thanks for the suggestions guys, i'll have a go - have tried on a shorter list and results look good so far using the vlookup(...,...,...,1) - the fuzzy search looks useful to future reference too!

 

[outlawdevil]

Alan, I had your code in my user define function, Is there an instruction on how to use fuzzyvlookup,fuzzyhlookup, and fuzzypercent? Thanks

 

[al_b_cnu]

Alan, I had your code in my user define function, Is there an instruction on how to use fuzzyvlookup,fuzzyhlookup, and fuzzypercent? Thanks

Hi outlawdevil,

Try the examples in this thread: http://www.mrexcel.com/forum/showthread.php?t=195635

or the comments at the top of the FuzzyVLookup function:

'********************************************************************************
'** Function to Fuzzy match LookupValue with entries in                        **
'** column 1 of table specified by TableArray.                                 **
'** TableArray must specify the top left cell of the range to be searched      **
'** The function stops scanning the table when an empty cell in column 1       **
'** is found.                                                                  **
'** For each entry in column 1 of the table, FuzzyPercent is called to match   **
'** LookupValue with the Table entry.                                          **
'** 'Rank' is an optional parameter which may take any value > 0               **
'**        (default 1) and causes the function to return the 'nth' best        **
'**         match (where 'n' is defined by 'Rank' parameter)                   **
'** If the 'Rank' match percentage < NFPercent (Default 5%), #N/A is returned. **
'** IndexNum is the column number of the entry in TableArray required to be    **
'** returned, as follows:                                                      **
'** If IndexNum > 0 and the 'Rank' percentage match is >= NFPercent            **
'**                 (Default 5%) the column entry indicated by IndexNum is     **
'**                 returned.                                                  **
'** if IndexNum = 0 and the 'Rank' percentage match is >= NFPercent            **
'**                 (Default 5%) the offset row (starting at 1) is returned.   **
'**                 This value can be used directly in the 'Index' function.   **
'**                                                                            **
'** Algorithm can take one of the following values:                            **
'** Algorithm = 1:                                                             **
'**     This algorithm is best suited for matching mis-spellings.              **
'**     For each character in 'String1', a search is performed on 'String2'.   **
'**     The search is deemed successful if a character is found in 'String2'   **
'**     within 3 characters of the current position.                           **
'**     A score is kept of matching characters which is returned as a          **
'**     percentage of the total possible score.                                **
'** Algorithm = 2:                                                             **
'**     This algorithm is best suited for matching sentences, or               **
'**     'firstname lastname' compared with 'lastname firstname' combinations   **
'**     A count of matching pairs, triplets, quadruplets etc. in 'String1' and **
'**     'String2' is returned as a percentage of the total possible.           **
'** Algorithm = 3: Both Algorithms 1 and 2 are performed.                      **
'** Algorithm = 4: Dan Ostrander's algorithm                                   **
'**                                                                            **
'** The following parameters allow matching by group, and only those values    **
'** which are in the group specified will be considered for matching.          **                  **
'** GroupColOffset                                                             **
'** This parameter specifies the offset column which contains the group values **
'** To trigger group matching, this must be a non-zero integer                 **
'** GroupValue                                                                 **
'** This parameter specifies the Group to be considered for matching           **
'********************************************************************************

Failing that, you can always PM me

 

[KurtVba]

Re: Closest match. Adding Descriptions for Function and its Arguments

Cool function & good team effort!

Starting in Excel 2010, descriptions for the function and its arguments / parameter can be added. Attached is some sample code I found at:

http://blogs.msdn.com/b/excel/archive/2006/05/17/600636.aspx

It also talks about work-arounds for earlier Excel versions.

Kurt

Sample VBA Code:

Option Explicit

Sub RegisterFucntion()
Dim vArgDescr(1 To 19) As Variant
Dim lCt As Long
For lCt = 1 To 19
vArgDescr(lCt) = "Description for argument # " & lCt
Next
Application.MacroOptions _
Macro:="UDFTest", Description:="Test function with
19 arguments", _
Category:="UDF Helper demo", _
ArgumentDescriptions:=vArgDescr
End Sub

Public Function UDFtest(Optional arg1, Optional arg2, Optional arg3, _
Optional arg4, Optional arg5, Optional arg6, Optional arg7, _
Optional arg8, Optional arg9, Optional arg10, _
Optional arg11, Optional arg12, Optional arg13, _
Optional arg14, Optional arg15, Optional arg16, _
Optional arg17, Optional arg18, Optional arg19) As String

UDFtest = "Test!"

End Function

 

[djreyrey]

Hello all,

I know this is an old post - hopefully someone can help me out. This is gonna sound so newbie of me, but hey, I am! How do I run this code?

I managed to add this code to a module but what do I do know? I need to be able to compare two columns to see which data matches. However, the data in each column will vary slightly for example, one column may University of Florida and the other column Univ of Florida. Or, St. Jude Mary and the other column Saint Jude Mary.

Thank you so much!!

Rey

 

[djreyrey]

Hello all,

I know this is an old post - hopefully someone can help me out. This is gonna sound so newbie of me, but hey, I am! How do I run this code?

I managed to add this code to a module but what do I do know? I need to be able to compare two columns to see which data matches. However, the data in each column will vary slightly for example, one column may University of Florida and the other column Univ of Florida. Or, St. Jude Mary and the other column Saint Jude Mary.

Thank you so much!!

Rey

Never mind! I kept reading the post and found instructions. Thank you!

 

[djreyrey]

This is working beautifly!!! Thank you so much!