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]

without using macros

I don't know what most of your data looks like, but it appears you'll need some type of VBA solution for this, consider:

http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html

 

[plant007]

Hi Sheetspread

Thanks for your help - looks like I will have to do manually as I am not a VBA person

Thanks for helping
Andy

 

[sheetspread]

No need to be, really, just paste it into the VBE and go........

 

[plant007]

No need to be, really, just paste it into the VBE and go........

Thanks, I feel to bend my datasets into the VBA code will take too long, but I have sent tot code to a collegue who is an expert so hopefully we can use at a later date

Thanks
Andy

 

[sheetspread]

It's just a vlookup, look at post #4 here:

http://www.mrexcel.com/forum/excel-questions/946821-formula-help-please.html

See how it returns fuzzy matches? You don't have to adjust the data.

 

[Maarten321]

Thanks, I feel to bend my datasets into the VBA code will take too long, but I have sent tot code to a collegue who is an expert so hopefully we can use at a later date

Thanks
Andy

Andy,

The fuzzy is actually pretty easy, just select which "Algorithm" you want to use in the formula.
This code that you paste in the vba can be seen as a custom formula.

So whenever you than use =fuzzy cell;cell;1 or 2 or 3(3 does both).
It will go into your vba to look what it needs to execute.

Biggest advantage of fuzzy is that you can set it to look for letter or word match (if i'm not mistaken)

Greetz

 

[plant007]

are you saying that the code allows the existence of the fuzzylookup equation with no changes to the code to match my dateset?

I have to go to a meeting now but will be back by 1.30 UK time.....

 

[sheetspread]

Yes, paste the code in a module, then on the worksheet type a vlookup formula as you did before except with the word fuzzy in front. Stating a confidence level is optional.

 

[plant007]

Yes, paste the code in a module, then on the worksheet type a vlookup formula as you did before except with the word fuzzy in front. Stating a confidence level is optional.

Hi Spreadsheet

I have added to the VBA editor and added fuzzy to the lookup equation. I was hoping that the 'FBC' part of 'Approval - HMT FBC' in the first workbook would be found in 'Full Business Case (FBC) for programme' in the second workbook, and then the equation would return the contents of the cell 6 columns from it, in this case someones name

Instead I got '*** 'NFPercent' must be a percentage > zero ***', which I assume is part of the macro code itself

Is this because I have only added the code to the first workbook? or is there something else I have not actioned

Thanks
Andy