Count if with partial text match (edited)

[nicholas.b.irwin]

Ok, this problem has been driving me nuts and I wanted to elicit some
advice from the experts. I will try to explain it the best that I can
and hopefully with a bit of illustration.

I have data spread over two different sheets. Sheet 1 looks like this
(shortened for clarity):

A
B
C
1 leacode leaname
state4math08
2 3001710 ABSAROKEE ELEMENTARY
3 3001740 ABSAROKEE HIGH SCHOOL
4 3001860 ALBERTON K-12 SCHOOLS
5 3017460 ALDER ELEMENTARY
6 3001950 ALZADA ELEMENTARY
7 3001980 AMSTERDAM ELEMENTARY

Sheet 2 looks like this:

A B
1 Name passed
2 Absarokee Elem 0.6
3 Amsterdam Elem 0.77

My goal is to have the values of column B in Sheet 2 counted in Column
C of Sheet 1 but only if the names from A2 matches the name in B2.
For clarity...the value of B2 (sheet 2) should go into C2 (sheet 1)
since A2 (sheet 2) matches B2 (sheet 1). I would then like to use the
same formula for the entire sheet.

As you have probably noticed, the texts do no match exactly. Though
they share a significant amount of letters, I can't seem to figure out
how to compensate for this.

I tried to troll around to find a suitable solution but I can't seem
to figure it out based on the help for other problems. I have an
inkling this may best be solved using VBA solution but lets see if I
can avoid that since my VB is really rusty.

Thanks in advance and let me know if I need to elaborate.

~Nic

 

[Shane Devenshire]

Hi,

The basic idea is of course
=VLOOKUP(C2,Sheet2!A$1:B$7,2,FALSE)

However, unless you can be specific about exactly what will be missing or
exactly how many characters will be present on the second sheet its not
possible. For example if ABSAROKEE is on the second sheet there is no way of
knowing which one it matches on sheet 1.

If on the other hand you knew that each name was a single word followed by
the type of school you could use something of this form:

=VLOOKUP(LEFT(C2,FIND(" ",C2)+1)&"*", Sheet2!A$1:B$7,2,FALSE)

 

[krcowen]

It seems like it would be worth the time to assign school numbers to
each school in a table somewhere. Then you could use numbers in your
lookups, return standard spellings in the school names, and save
yourself a lot of grief.

Use Advance filter to make unique lists of all the school names from
both lists. You can use those lists to figure out a list of schools
you want to live with; a few sorts, copy downs, and a handful of
search and replaces and you will be way better off. Even for a very
significant numbers of schools, this will go pretty quickly and be
worhwhile.

Otherwise, as Shane noted, your task is pretty much impossible.

Good luck

Ken