PDA

View Full Version : [SOLVED] Matching a string with a partial string using match function



sllaksvb
09-07-2017, 01:33 PM
Hi all,


s = Application.IfError(Application.Match(Sheets("GetCodes").Cells(i, 3), Sheets("Entities").Range("1:1"), True), 0)

I am trying to get my macro to return 0 if there isnt a match between my value in Sheets("GetCodes") and Sheets("Entities"). If there is a match, my macro will continue running. It seems to be working perfectly, however I have one cell in Sheet("GetCodes") that value is "UBB GE London Branch" and it does not match with the value "UBB" in Sheet("Entities"). How can I amend the above code so that "UBB GE London Branch" would be a match with "UBB" and not return a value of 0 when running this code?

Any help would be greatly appreciated! Thank you!

offthelip
09-07-2017, 04:24 PM
The solution depends on what all the other data looks like. i.e if you just tried to match the first three characters of all the codes would that mean you got multiple matches? or do you just want to deal with this particular code as an exception? In which case just test for UBB GE London Branch and deal with separately.

sllaksvb
09-08-2017, 06:25 AM
Hi offthelip,

The other data are all very unique. Therefore, if I tried to match the first three characters of all the codes, none of the other data would contain UBB and there would be no match. However, I am also matching other data that would require matching of more than the first three characters. Would it be possible to do a match when it contains a similar name?

offthelip
09-08-2017, 06:43 AM
I think the way I would try to solve this would be to vary the length of the string to match depending on what the first letter (or maybe first two letters) of the string to match are, thus you can vary the match depending on what the string is. You will need to analyse your data to ensure that you do get the match that you want.
Another possible way would be to create a table of aliases, i.e. when you translate specific names to the names in your "entities" sheet before you try the match
This depends on how many names you need to alter to get a match.

sllaksvb
09-08-2017, 09:00 AM
Thank you for your help. I will perform the analysis and determine the best approach to this situation! Thank you.