Partial matches

[plant007]

Hi

I am really struggling with matching text strings across two datasets which use different terminology. I have used a vlookup (within an IF and ISNA), with wildcards to match certain text strings but this only works when the original text string is part of the second text string ie SoS is part of ABC SOS DEF, but most of the matches I need to make are as follows;

GHI SOS JKL as part of ABC SOS DEF

which means I need to match 3-4 contiguous letters within both text strings, without using macros

Any help would be most appreciated

Thanks
Any

 

[sheetspread]

Which formula did you type?

 

[sheetspread]

Don't type a true/false or 0/1 approximate match, just Lookupvalue, Tablearray, and Indexnum. There's also an optional NFPercent and Rank which you may not need to change, and Algorithm, which I think you should change to 2 (default is 3)

Here's an example (separate workbooks):

Excel 2010
	B	C	D	E	F	G
4	Full Business Case (ABC) for programme					g
5	quick west east (QWE)					n
6	xtra care variance (XCV)					z
7	Full Business Case (FBC) for programme					r
8	Full Business Case (JBC) for programme					l
Sheet4

Excel 2010
	P	Q
6	Approval - HMT FBC	r
Sheet1

Cell Formulas
Range	Formula
Q6	=FuzzyVLookup(P6,Sheet4!B$4:G$8,6,,2)

 

[plant007]

I'm leaving work today but Ill look at this at home tomorrow - thanks again for all your help on this!

 

[plant007]

I'm leaving work today but Ill look at this at home tomorrow - thanks again for all your help on this!

Hi Spreadsheet

I have tried the exact equation and I am getting an answer, although it was the wrong one...I changed the rank from 2 to 1 with no luck and finally 3 which worked 3. I tried another and it rankled 8 to get the right person

Any thoughts - I don't think I can use different ranks in each row (and I am slightly surprised at the rankings it is giving for the right answers

Thanks
Andy

 

[sheetspread]

Are you able to post some sample data here?

 

[plant007]

Are you able to post some sample data here?

Not really I'm afraid, although I can do titles - I work on data for projects

First spreadsheet where original data is located ie Approval - HMT FBC (B52)

1. Reviews (column B)
2. Client (column C)
3. Project Responsibility (column D)
4. Last Review Date (column E)

Range B7:E87

Second spreadsheet (range B9:G54), which is where the search is looking, so I am hoping 'Full Business Case (FBC) for programme' gets picked up by the fuzzy logic search on the term in spreadsheet 1, and then returns the person responsible (6) (=fuzzyvlookup(B52,'Project IAAP'!$B$9:$G$54,6,,3))

1. Activity / Product (column B)
2. Assurance/Approval Start Date (Column C)
3. Assurance/Approval End Date (Column D)
4. Last assurance/approval date (Column E)
5. Next assurance/approval date (Column F)
6. Person responsible (Column G)

Thanks
Andy

 

[sheetspread]

Then can you try to change the confidential data or invent some with a similar pattern? If two strings are similar enough to match it should be possible via the above method. Otherwise, there are other advanced techniques such as regular expressions (also VBA).

 

[plant007]

Here you go - it actually works on this one

[TABLE="width: 898"]
<tbody>[TR]
[TD][/TD]
[TD]Spreadsheet 1[/TD]
[TD][/TD]
[TD]fuzzyvlookup(B12,$B$20:$G$25,6,,1)[/TD]
[TD]Mr Adam[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Reviews
(use as applicable)[/TD]
[TD]Client[/TD]
[TD]Project Responsibility[/TD]
[TD]Last Review Date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]AZX TR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]BBB LP[/TD]
[TD]CEO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]CCXZ JK[/TD]
[TD]CEO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]DPO RT[/TD]
[TD]CEO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]EGY GO[/TD]
[TD]CEO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]FYY GG[/TD]
[TD]CEO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]GGO DF[/TD]
[TD]CEO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]HXX GH[/TD]
[TD]CEO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]IZZ WE[/TD]
[TD]CEO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]JXZ GO[/TD]
[TD]CEO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Spreadsheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Activity / Product[/TD]
[TD]Assurance/Approval Start date[/TD]
[TD]Assurance/Approval End date[/TD]
[TD]Last assurance/approval date[/TD]
[TD]Next assurance/approval date[/TD]
[TD]Person responsible[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]AAABC GO[/TD]
[TD="align: right"]01/02/1974[/TD]
[TD="align: right"]02/02/1974[/TD]
[TD="align: right"]02/02/1974[/TD]
[TD="align: right"]02/02/1980[/TD]
[TD]Mr Bill[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]DDDEF[/TD]
[TD="align: right"]01/02/1977[/TD]
[TD="align: right"]02/02/1983[/TD]
[TD="align: right"]02/02/1983[/TD]
[TD="align: right"]02/02/1986[/TD]
[TD]Mr Sid[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]GGOGHI[/TD]
[TD="align: right"]01/02/1981[/TD]
[TD="align: right"]02/02/1988[/TD]
[TD="align: right"]02/02/1988[/TD]
[TD="align: right"]02/02/1980[/TD]
[TD]Mr Adam[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]JJJKLGG[/TD]
[TD="align: right"]01/02/1988[/TD]
[TD="align: right"]02/02/1990[/TD]
[TD="align: right"]02/02/1990[/TD]
[TD="align: right"]02/02/2000[/TD]
[TD]Ms Sonia[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]MMMNO[/TD]
[TD="align: right"]01/02/1992[/TD]
[TD="align: right"]02/02/1999[/TD]
[TD="align: right"]02/02/1999[/TD]
[TD="align: right"]02/02/2009[/TD]
[TD]Ms Sarah[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD]PPPQR[/TD]
[TD="align: right"]01/02/1999[/TD]
[TD="align: right"]02/02/2002[/TD]
[TD="align: right"]02/02/2002[/TD]
[TD="align: right"]02/02/2008[/TD]
[TD]Ms Linda[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F
1	Reviews	Client	Project Responsibility	Last Review Date
2	AZX TR		#N/A	#N/A
3	BBB LP	CEO	#N/A	#N/A
4	CCXZ JK	CEO	#N/A	#N/A
5	DPO RT	CEO	Mr Sid	2/2/1983
6	EGY GO	CEO	Mr Adam	2/2/1988
7	FYY GG	CEO	Mr Adam	2/2/1988
8	GGO DF	CEO	Ms Sarah	2/2/1999
9	HXX GH	CEO	Mr Bill	2/2/1974
10	IZZ WE	CEO	#N/A	#N/A
11	JXZ GO	CEO	Ms Sonia	2/2/1990
12
13
14
15	Activity / Product	Assurance/Approval Start date	Assurance/Approval End date	Last assurance/approval date	Next assurance/approval date	Person responsible
16	AAABC GO	1/2/1974	2/2/1974	2/2/1974	2/2/1980	Mr Bill
17	DDDEF	1/2/1977	2/2/1983	2/2/1983	2/2/1986	Mr Sid
18	GGOGHI	1/2/1981	2/2/1988	2/2/1988	2/2/1980	Mr Adam
19	JJJKLGG	1/2/1988	2/2/1990	2/2/1990	2/2/2000	Ms Sonia
20	MMMNO	1/2/1992	2/2/1999	2/2/1999	2/2/2009	Ms Sarah
21	PPPQR	1/2/1999	2/2/2002	2/2/2002	2/2/2008	Ms Linda
Sheet3

Cell Formulas
Range	Formula
C2	=FuzzyVLookup($A2,$A$16:$F$21,6,,2)
D2	=FuzzyVLookup($A2,$A$16:$F$21,4,,2)

The "algorithm" term is fixed. I see you've provided random text, but is there really no pattern that can be matched?

 

[plant007]

Hi Sheetspread

Not really, it could just be a few letters ie FBC, as many of the reviews have 3-4 letter acronyms. In the second spreadsheet, it's more of a narrative and so terminology can be different ie FBC in full...I am not sure its possible to get this 100% right but I was hoping that FBC and those alike would be picked up and can't understand why higher ranked results have no reference to FBC / Approval etc at all...confused although feel solution close!