Hi,
I am trying to match a cell with another cell. The two cells are not standardized but basically contain the same information. I have attached a sample of my worksheet.
For instance, I am trying to match South Methodist University with Dedman School of Law, Southern Methodist University.
The index and match function work well with the "*'& &"*", but it sometimes returns the first result of the data instead of the best. Any suggestions on the function? Thanks.
Hi
This will get you most of the way, but you have some names in A that are pretty jumbled compared to H....
=VLOOKUP("*"&LEFT(A2,20)&"*",$H$2:$I$15,2,0)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I see what your saying. Maybe this will help? I've attached a new file. Can you match the row or the set of keywords with the respective id? Thanks.
Hi,
Your data really needs some sort of Fuzzy Lookup functionality since wildcards with VLOOKUP will not find all your apparent matches. There's no standard fuzzy functionality in Excel, but for Excel 2010 Microsoft did develop a Fuzzy Lookup Add In which you can find here http://www.microsoft.com/en-gb/downl....aspx?id=15011
I've just tried with your copy of the workbook and it seems to deliver the results you expect.
If you have Excel 2010 for Windows then you could try that. Excel for Mac 2008 of course doesn't support VBA and I'm not sure whether the 2011 for Mac would support the Add In.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
All you have done there, is break the words apart (and left out a few connectors - of), that really doesnt change the contents/context of your data.
For instance, you could look at...
Southern Law (Memphis State)
and see that it "matches"...
University of Memphis, Cecil C. Humphreys School of Law
But I would not have been able to match those 2, even just using my eye - the only reason i even know it matches is because you said it does in your "expected outcome"
So, having said that, is there some "rule" that ypou would apply to try and achieve what you want? How would you do this manually?
I see what your saying FDibbins. I thought by eliminating the superfluous data that it would work but it does not. The way I would do it manually is to visually see if both law schools matched and then put in the respective lawid. I didn't have a problem with matching the data. The problem I had was getting the matched data. For instance, I would have a sumproduct(--(isnumber(search))) function that would match the data pretty well, but I have yet to find a way to get the matched data. Any Suggestions? Thanks.
Do you only have access to Excel for Mac 2008 - see post #4 which does offer a solution with an add in but you need Excel 2010/2013?
Im sorry, off the top of my head, I dont. But I will post this thread to other experts and see if they can come up with anything for you
Okay thanks FDibbins!
I have a desktop that runs excel 2013 and I downloaded the add-in and it seems to be working well. I'll tell you definitively the results on monday!
OK,
I'll be interested to hear how you get on with your whole data sets not just the sample you uploaded. I set the Similarity Threshold slider well to the left of the centre, it may have even been on the far left. Presumably it's there to give some sort of trade off between quickness of operation and trapping larger and larger subsets of all fuzzy matches.
I have yet to try Alan's fuzzy lookup, but the Microsoft version works perfectly. Other than the names that are entirely different from their respective school, it seems to match every school. Thanks for all the help!
Thanks for the assist, Richard
Also, one of the best fuzzy codes out there is Alan's Fuzzy Lookup (http://www.mrexcel.com/forum/excel-q...ml#post3145162). He keeps improving on it from time to time and has added Soundex, etc. It's really worth checking out.
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks