Microsoft Excel - How often do you use it?

[Coach]

For advanced users, tell us some of your tricks in terms of using the formulas and such.

For beginners, ask questions here, hopefully answers could be provided.

As for me, my question to the experts/advanced users,

Let's say if I have a spreadsheet with column A that has names, typically last name, first name and middle initial (example: Smith, Joe Steve). Column B has names that is typically the same as column A, except it would be missing the middle name or a misspelling on the first name (example: Smith, Joe).


Column A would have say 450 names and Column B would have 150. How can a "near" match search be found, after utilizing the conditional formatting that highlights duplicate values?

[C3HIEF3S]

Can you post an example of some of the rows in the columns that you’re working with and what you want your end-result to be?

[Coach]

Originally Posted by C3HIEF3S

Can you post an example of some of the rows in the columns that you’re working with and what you want your end-result to be?

See attached.

As you can see, the left would have the full name while on the right, some will already have a full name (which can be easily found by using the conditional format for duplicate values). Others will have a partial due to missing middle initial/name or partial first name.

My end result would be is to find column A (which is like roughly 25-50 names) and on column C, it would have a complete roster of who joined (in excess of 700 names), in a yearly cycle.


Basically, I just want to find a functional or something that allows me to find the person's full name on the left to have a probability of matching the name on column C.

For example, Feunutes, Shannon Arnold on the left is Feunutes, S. But because of Column C is larger, I don't want to have to use search and find each individual names independently/manually, if that makes sense?

[eDave]

Originally Posted by Coach

See attached.

As you can see, the left would have the full name while on the right, some will already have a full name (which can be easily found by using the conditional format for duplicate values). Others will have a partial due to missing middle initial/name or partial first name.

My end result would be is to find column A (which is like roughly 25-50 names) and on column C, it would have a complete roster of who joined (in excess of 700 names), in a yearly cycle.


Basically, I just want to find a functional or something that allows me to find the person's full name on the left to have a probability of matching the name on column C.

For example, Feunutes, Shannon Arnold on the left is Feunutes, S. But because of Column C is larger, I don't want to have to use search and find each individual names independently/manually, if that makes sense?

My own personal fetish I guess, but I prefer separate tabs for each table. Maybe sadistic is a better term.

[Coach]

Originally Posted by eDave

My own personal fetish I guess, but I prefer separate tabs for each table. Maybe sadistic is a better term.

I have heard of VLOOKUP, but I haven't had the opportunity to use it in a practical setting, you know?

I tried to experiment it, but just can't seem to find the one that would help.

[Discuss Thrower]

Originally Posted by Coach

I have heard of VLOOKUP, but I haven't had the opportunity to use it in a practical setting, you know?

I tried to experiment it, but just can't seem to find the one that would help.

Index + match is better.

[kccrow]

Originally Posted by Coach

See attached.

As you can see, the left would have the full name while on the right, some will already have a full name (which can be easily found by using the conditional format for duplicate values). Others will have a partial due to missing middle initial/name or partial first name.

My end result would be is to find column A (which is like roughly 25-50 names) and on column C, it would have a complete roster of who joined (in excess of 700 names), in a yearly cycle.


Basically, I just want to find a functional or something that allows me to find the person's full name on the left to have a probability of matching the name on column C.

For example, Feunutes, Shannon Arnold on the left is Feunutes, S. But because of Column C is larger, I don't want to have to use search and find each individual names independently/manually, if that makes sense?

I'll put something together for you but you're not really going to be able to get a near match. What you'll have to ensure that at least the portion of LastName, FirstInitial is in both and you could get something reasonable using a combination of a column with a LEFT/FIND based formula and then a INDEX/MATCH based formula.

[kccrow]

Originally Posted by Coach

See attached.

As you can see, the left would have the full name while on the right, some will already have a full name (which can be easily found by using the conditional format for duplicate values). Others will have a partial due to missing middle initial/name or partial first name.

My end result would be is to find column A (which is like roughly 25-50 names) and on column C, it would have a complete roster of who joined (in excess of 700 names), in a yearly cycle.


Basically, I just want to find a functional or something that allows me to find the person's full name on the left to have a probability of matching the name on column C.

For example, Feunutes, Shannon Arnold on the left is Feunutes, S. But because of Column C is larger, I don't want to have to use search and find each individual names independently/manually, if that makes sense?

Column B Formula: =IF(IFERROR(MATCH(IFERROR(CONCATENATE(TEXT(LEFT(A2,FIND(",",A2)-1),""),", ",TEXT(LEFT(MID(A2,FIND(" ",A2)+1,256),1),"")),""),$D$2:$D$16,0),0)<>0,"On Roster","Not On Roster")
Column D Formula: =IFERROR(CONCATENATE(TEXT(LEFT(C2,FIND(",",C2)-1),""),", ",TEXT(LEFT(MID(C2,FIND(" ",C2)+1,256),1),"")),"")

What it will look like...

[kccrow]

You could also separate it into more columns if you want... such as...
Column B Formula =IFERROR(CONCATENATE(TEXT(LEFT(A2,FIND(",",A2)-1),""),", ",TEXT(LEFT(MID(A2,FIND(" ",A2)+1,256),1),"")),"")

Column E Formula
=IFERROR(CONCATENATE(TEXT(LEFT(D2,FIND(",",D2)-1),""),", ",TEXT(LEFT(MID(D2,FIND(" ",D2)+1,256),1),"")),"")

Column G Formula
=IF(IFERROR(MATCH(IFERROR(CONCATENATE(TEXT(LEFT(A2,FIND(",",A2)-1),""),", ",TEXT(LEFT(MID(A2,FIND(" ",A2)+1,256),1),"")),""),$E$2:$E$16,0),0)<>0,"On Roster","Not On Roster")

Looks like this:

[Coach]

Originally Posted by kccrow

You could also separate it into more columns if you want... such as...
Column B Formula =IFERROR(CONCATENATE(TEXT(LEFT(A2,FIND(",",A2)-1),""),", ",TEXT(LEFT(MID(A2,FIND(" ",A2)+1,256),1),"")),"")

Column E Formula
=IFERROR(CONCATENATE(TEXT(LEFT(D2,FIND(",",D2)-1),""),", ",TEXT(LEFT(MID(D2,FIND(" ",D2)+1,256),1),"")),"")

Column G Formula
=IF(IFERROR(MATCH(IFERROR(CONCATENATE(TEXT(LEFT(A2,FIND(",",A2)-1),""),", ",TEXT(LEFT(MID(A2,FIND(" ",A2)+1,256),1),"")),""),$E$2:$E$16,0),0)<>0,"On Roster","Not On Roster")

Looks like this:

That looks pretty good, however, I'm curious as to why Davids T isn't on the roster (Column G), even though the short names match?

[eDave]

Excel is closed till Monday.

[digger]

Maybe
=exact(left(A2,5),left(B2,5))

[digger]

or


=lookup(left(A2,5),B$2:B$150)

[eDave]

Originally Posted by digger

or


=lookup(left(A2,5),B$2:B$150)

This one.

[digger]

=lookup(left(A2,5),C$2:C$150) and =left(C2,5)