this video i'm gonna show you how we can search and find similar text in google sheets so in
this particular example right here i just found a list of misspellings on a web for different states
and here on the right we have an accurate list this is our list of states right here and then
some information related to those so that's pretty much the data we have now what i'm gonna do
i'm gonna try to find an accurate match for this now if you know something like a vlookup or match
function then you know that you won't be able to do this using those functions you can only
use those if you have an exact match okay so to be able to match these i'm going to go here and
do equals find similar text and this function is not going to be available by default in your
spreadsheet i'm going to show you by the end of the video how you can actually install this
so you can use this function but for now i'm going to show you how to use this so i'm going
to do this find similar text then i'm going to point to this target phrase parameter being this
comma then i need a phrase list that i'm matching this to so for that i'm just going to select
this range of states and i'm going to just drop the end reference to go all the way down i'm
gonna press f4 for absolute reference so that range stays in place you can also just manually
do these dollar signs if you want comma and then finally we need this thing that's called minimum
score this minimum score is gonna go from zero to one and one means it has to be matched exactly so
if you lower it basically you're saying it can be more loose in the way we match this just to give
you an example if i do one right now this return type is optional if i do this i hit enter see this
line there's no match we get nothing now if i drag this down i'm going to double click in this corner
as you can see we didn't get any matches whatsoever because these are all mistypes
so let's go and fix one of them so let's say we have this one it should be south carolina
so we're going to add that i so it's not a mis-type anymore and now because it's a
match we will go grab south carolina from this list which is down here someplace see this one
and we're basically just gonna bring it over so that's a match but i really just want to match
more of these so now i can just go here and lower the score so let's try something like 0.8
and see what that's going to look like so i'm going to do that and i'm going to drag this down
and as you can see we're getting some matches at this point so if i scroll up you'll see with
0.8 score we were able to get quite a few matches we didn't match everything as you
can see so if we want to match more then we want to basically further lower that score
but as we do this we want to be careful because at some point we're going to get false positives
so i'm going to go here and try for example 0.6 you can also do like 0.65 or something
i'll just go with this 0.6 i'm going to drag that formula down and let's see what that looks like
now so that's now a lot better so you can see we're able to match all of this and the match
is actually accurate so you want to be paying attention when you use tools like this because
you're basically making a loose match which could potentially match things that it shouldn't match
now some of this like this one i don't think we're ever gonna match that that's just way too
far from what we have here right in our original data so as we do this we also want to be realistic
what's going to be possible but i'm going to try to even further lower the score so let's try 0.4
and as you can see now i got a lot more matches
this one we didn't match but i don't think that's ever gonna match that's just way too far
the rest of this actually looked pretty good so again at this point you can just decide do you
want to even try lower or whatever your match is good enough for what you're trying to do
right so i can even now lower the score to say 0.2 and we should be able to possibly match
even more of those i'm even surprised that is actually accurate right there
but let's take a look if we can find a mismatch of some sort like see like this one so because of
lowering the score now we also have this that should not be a match so this is what i was saying
is that as you lower the score you want to stop at some point because if you go too low at some point
you're going to get all these false positives and maybe you're fine with them and you want to
go over those and fix those or maybe it's better to just match less so i'll just go to that 0.4
so with tools like this you basically just want to be realistic because it's never going to be 100
so you want it to help you as much as possible to get closer to what you need so now let's say
this is good this is pretty much where i'm gonna stay with my number there so at this point i may
want to grab some relevant information from here maybe we want to get the short version
of states here now we could do that with a vlookup at this point so i can just call it state and then
we can technically just now use a vlookup using now this value that we just found and basically
we can use it over this data on the right like so i'm going to drop the end reference to f4
absolute reference and then we'll do column number two and zero for our exact match so
we could use now vlookup and i'm gonna use that to drag it down obviously for the ones we didn't
find we're not gonna have any matches so now you can see how we can use vlookup here to go
grab the results from the second column based on these matches that we found in this new column
right here from our other table now another way to go about this is to use a different argument
let's just delete this in here and this function also has fourth argument called position
and this fourth argument it can be position or it can be text
so when we don't do anything it's the same as doing text but if i switch this to position
and drag this down see we're going to get the position in which it was able to find this so for
example if we look at this connecticut right here it says 7 that means in this list it's number 1 2
3 4 5 6 7. so it finds the position of that item so what we could do then we could just use that
number now to go grab information from the other table so for example if i wanted to get that short
state i could do well i want to handle this blanks so i'm going to say if this
equals blank then return blank double quotes in all the other cases i'm going to do index
and i'm going to point to the column where i want this information from so starting from here
going down i'm going to drop that end reference press f4 to make this absolute reference comma
and as a row position i'm going to point to that position returned from this function close
parentheses close parentheses and we drag it down as you can see we got our matches
just like that and this way we can repeatedly use this column to get other information from other
columns so now if i also wanted to add population i could basically just do the same thing
i would say if this is blank then leave it blank comma otherwise let's do index function the only
difference this time instead of selecting this i'm going to select this population column drop the
and reference f4 comma and as a row number we're going to point to this row number right there
and that should basically grab us population from that particular row if you want more detail
understanding how this index function works and if statements all of that i
have separate videos you can watch index match videos and if statement videos
if the speed i'm going at is too fast so now we should be able to get those so for
example if we look at i don't know hawaii see it says 1.4 so that should be coming from here
see so depending on your data this may work better in some cases worse in some other cases
just try to pay attention and see what happens right so one more example for example we have this
list see it gives us all these different companies and then for those companies we have these other
tickers list here and we might be able to get the right company match and then using that we
can get the right stock number so the spelling is probably not going to be the same so for example
here we have walmart stores let's go see what that looks like in here see it's walmart stores
inc so let's try to match these and see how well it's going to work so
i'm going to go here and add a column i'm going to just call it matching name
and then again we'll do equals find similar text will point to this comma and as a phrase list i'll
go to my tickers list and basically just select this list to wrap the end reference to go all the
way down f4 for absolute reference comma and then we have minimum scores so let's try 0.7 ish and
see what this looks like so i'm going to close parentheses hit enter and as you can see that's
actually accurate that's the match from the other list drop this down and see how well this goes
so this list is a lot longer so it's going to take longer for this to process this as you can see
so the ones that we actually found so far look pretty good now we do have a lot that we didn't
find now i don't really know how many of these are actually in the other list but so far at least the
ones that i see that are a match look pretty good i'm pretty sure there's also going to be
some that probably are not good but if we want to be able to maybe match more we can again lower the
score and see what happens so i'll try something like 0.5 this time let's see how bad that looks
so i'll just drag that down let's just give it some time to recalculate all of that
all right so as you can see now we're able to find a lot more and looking at this i actually
like this results let's keep scrolling down i'm pretty sure at some point we're gonna
find some things that shouldn't match like that looks good that looks good now that see it's not
a match so again this is what i was talking about you have to understand that at some point as we
lower that score we're also likely to find things that we shouldn't find like none of those
should actually match but again like i said this is never going to be perfect so basically we want
to do the best we can with the tool we have to get closer to what we need and once we get the
list that we actually need and we actually quality control this and we're happy with this results
let's actually just truncate the rest so we don't have to look at it now we can match the
other column by doing a vlookup again so i'll go to this other column here do a vlookup point to
this comma and as a range i'll go to my source and select something like this drop the end
reference f4 to make it absolute reference i want the results from column number two
and then this is going to be an exact match zero so at this point we'll drag this down
and hopefully we'll find the accurate one
now let me show you how you set it up so you actually have this function available because
like i said by default this function it's not going to be available in your spreadsheet
so i just removed it so that we get an error because that function by default is not available
let's add that function so what you're going to need you're going to need this github link
that i'm going to put in video description so you can get to it and you're going to open your
spreadsheet you're going to go to extensions and go to apps script here on top left you want to
click and name this something unique and then you want to basically create three script files here
and you want to name those scripts in a way that makes sense even though the name exactly doesn't
matter so if you go here the last one right now is this v 1.1 if i click on that see it gives me
three of these so i'm gonna start with this last one i'm gonna open that i'm gonna copy this name
that's what i'm gonna use in my script name so i'm gonna copy that go back to this click
script paste that name here and then as a script i'm going to delete what's in here go back to
this page and literally just select everything in the script box make sure you only select what's
in here don't miss anything but also don't select extra stuff so we're going to copy this from here
go to this screen and paste and just hit save or command control s to save this
now once we do this we're going to have to repeat this for the other two so it's important that you
do this in the same order that i'm doing this so first this then i'm gonna do the second one so
i'm gonna click on this one and again i'll grab the name for this go back here create
a script file with that name and then i'm gonna go back here and copy this script
go delete to whatever's here paste over it and save and then we need to repeat this for the
third one so i'm gonna go back and do here this is the one i'm gonna click on that copy this name
go back add script paste that name delete whatever is in here go to this
particular code copied go back to this and paste
so now i got all three of them
so once you have all three of those you can save this whole thing close it
you may need to refresh this spreadsheet after you do this so if it doesn't load right away
just refresh the spreadsheet you have
and see right now it was saying unknown function but now it's going to recognize that function in
a second and there it is now it works so now we should be able to do find similar text there it is
the function will show up and you can work with this and that should now also
work here so that should do it for this video thanks for watching and i'll see you next one