Solved VBA code for finding partial/full match values!

April 5, 2017 at 14:58:42
Specs: Windows 64, AMD 6300/8GB
Hi,

I have to find the matching value from a list of pre-defined list. The problem is the searching text is not formatted the same as those pre-defined values.

Pre-defined values on column A (A2-A151) are like:

Account ID
Account Name
Video Played 25%
Video Played 50%

But the search items comes like:

AccountID
AccountName
Video Played 25
VideoPlayed50

Is there a way to find the most close match through VBA and put it next to the search item cells? Example:

COLUMN B | COLUMN C
AccountID | Account ID
AccountName | Account Name
Video Played 25 | Video Played 25%
VideoPlayed50 | Video Played 50%


I have my utmost respect for you guys as you have helped me a lot of times already! Thanks.

~ Dollar

message edited by dollar5474


See More: VBA code for finding partial/full match values!

Report •

#1
April 5, 2017 at 16:32:17
✔ Best Answer
Before we go too far writing code based on your examples, I have to ask a question.

You said:

But the search items comes like:

AccountID
AccountName
Video Played 25
VideoPlayed50

3 of the 4 search items have the spaces removed but 1 of them doesn't. That leads me to assume that there is no consistency in how your search strings differ from the main list.

Inconsistency and VBA don't get along very well.

Since we only have your short list of examples to work with, my first suggestion would be to convert both the main list and search string to something more consistent. I have no idea if this will work for what you are trying to do, but if you apply the following formula to both lists, you will end up with exact matches. (The spaces and percent signs will be removed)

=SUBSTITUTE(SUBSTITUTE(A4," ",""),"%","")

Once you have exact matches, perhaps your match task will be doable.

BTW You can keep Nesting SUBSTITUTE functions if you need to remove other characters than a space and percent sign. The formula won't fail if any characters) don't exist in a given cell.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4," ",""),"%",""),"5","")

Does that help in any way?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 5, 2017 at 16:55:54
Hi,

Yes! Your suggestion should have worked just fine for the example part. However, there is no consistency or pattern in the data that I have to match. Please see another example when the result partially matches:

https://www.screencast.com/t/be3k73...

In this example there are some extra text parts which are not in the search items. I can surely share the excel doc containing all the data, if you want.

Thank you for your help and as always I appreciate it very much!

~ Dollar


Report •

#3
April 5, 2017 at 18:42:59
My first inclination is to pass on this one and say it can't be done, at least not without so much coding that you might as well do it manually.

For example, consider the Paused Rate cells. Sure, you could write code to look for PausedRate, but that would find both cells with Paused Rate in them. Now you need another instruction so that the code can tell the difference between the 2 cells. However, you don't have a cell with Pause Rate and FS in it. You'd need something that tells the code that when it searches for FS it actually needs to find Full Screen. Now we're getting so specific, we're almost doing the searches manually. You'd have to account for each occurrence of "when I say find this, I really mean I want you to find that".

Also consider FullPlayRate. You'd have to parse the Full and Play and Rate strings and tell the code to find all three strings in a single cell because you can't expect the code to find FullPlayRate since it doesn't exist in any cell even with spaces removed. FullyPlayedRate is very different than FullPlayRate. Plus you have FullPlays (with an "s") and you want the code to find Fully Played, but a different Fully Played than the Fully Played in the cell with "Rate". You are bordering on expecting VBA to find Bananas when searching for Apples. (And I'm getting dizzy just trying to explain the complexity of what you are trying to do)

I don't do this very often, but I'm going to go with my initial inclination and pass on this one. If someone else wants to take a shot at it, maybe they can figure out a way to do it.


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 5, 2017 at 20:01:56
I understand your point! Thank you for the help and explanation. Have a good day! ☺

Report •

#5
April 6, 2017 at 03:45:00
Would it be possible to use 2 Helper Columns to assign a value to each item in the search list and then assign the same value to the matching item in the main list? Then you could match the values instead of the strings.

Yes, the initial set up is manual, but depending on the purpose of this task, perhaps that would make it doable.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 6, 2017 at 06:14:36
Derby,

Just an idea, don't really have the time to play with it, but would we not be able to split the joined words and check if they exist in the fields being tested??

Function SplitWords(ByVal Str As String) As String

EG:

FullPlayRate

becomes

Full Play Rate

Then check if each of these words exist in a cell and if so then that's a match?

The code below splits on capitalized letters in a joined string

Function SplitWords(ByVal Str As String) As String

    Dim I As Integer
    SplitWords = Left(Str, 1)
    For I = 2 To Len(Trim(Str))
        If (Asc(Mid(Str, I, 1)) > 64) And _
           (Asc(Mid(Str, I, 1)) < 91) And _
           (Mid(Str, I - 1, 1) <> " ") Then _
            SplitWords = SplitWords & " "
        SplitWords = SplitWords & Mid(Str, I, 1)
    Next
End Function

message edited by AlwaysWillingToLearn


Report •

#7
April 6, 2017 at 06:51:55
Sure that works for that match.

But what about FullPlays? "Plays" (with the "s") doesn't exist in the cell that is a supposed match, Video Fully Played. You can't just split out the Full and Play (i.e drop the s) because that will result in a match with the Video Fully Played Rate cell. You'd need to also ignore a cell that contains Rate for that search string.

The same holds for FSPausedRate. That's supposed to be a match for Video Full Screen Paused Rate. Where's the FS? The code needs some way to know that FS is a match for Full Screen. That's not that hard to do, but you then have to also deal with the fact that you aren't just matching FS to Full Screen, you are also have to deal with the Paused Rate part.

I'm not saying it's not possible. Let's just say I don't feel like dealing with all those unmatched matches. Feel free to give it a shot. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
April 6, 2017 at 06:59:02
I agree theres a lot of work to do to get this to work, too many unmatched values, I too don't have the time nor the patients for doing this. Maybe in a couple of days if I get enough sleep I may :) anyways hope all is well.

Report •

#9
April 6, 2017 at 10:36:27
Guys you have already helped so much! Don't stress it much longer. I appreciate all your support. :)

Report •

#10
April 6, 2017 at 11:53:00
Hi Derby,

The SUBSTITUTE formula combined with VLOOKUP is holding up nicely so far. Thank you all.


Report •

#11
April 6, 2017 at 12:33:23
Color me surprised, but if it works for you, I'm glad I could help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question