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?

[eDave]

VLOOKUP will change your life.

[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.

[Coach]

Originally Posted by Discuss Thrower

Index + match is better.

Okay, what is Index + match? How does that process work?

[Discuss Thrower]

Originally Posted by Coach

Okay, what is Index + match? How does that process work?

Not to be glib but Googling how-to will be better than me trying to explain.. since I have to refer back to a finance-bro meme to replicate it.

But index+match has the same functionality of LOOKUPs but with better flexibility.

[CapsLockKey]

Originally Posted by eDave

VLOOKUP will change your life.

That and pivot tables

[eDave]

Originally Posted by CapsLockKey

That and pivot tables

OMG! Pivot tables...

https://tenor.com/view/jonah-hill-ya...os-gif-7212866

[KCUnited]

Originally Posted by C3HIEF3S

My company is on this route as well, but only at our corporate HQ.

Excel is a must-know tool in the business world, but so often misused and isn’t practical for doing things such as in-depth data analysis.. When I was in school, I had professors in some data science courses I took dock students points for using Excel for anything (as simple as string-parsing on a column) on assignments.

“Excel is a glorified text-editor”, a phrase I recall being said to us after such an instance happened.

If you ever want to know if someone is bullshitting on their resume, test them on those Excel skills they always list that they have. I’d bet the majority of people wouldn’t even know what a VLOOKUP is.

Sounds like my company, there’s departments where Excel use isn’t allowed. I could see listing Excel on a resume potentially hurting as those habits are hard to let go by long term users when introduced to new tools. Myself included.

[DaFace]

What the hell did you guys do to the table formatting in this thread?

[DaFace]

Found it. It was digger's fault. Fixed now.

[Third Eye]

Originally Posted by Discuss Thrower

Index + match is better.

A million times this. VLOOKUP sucks.

[Third Eye]

Originally Posted by Discuss Thrower

Not to be glib but Googling how-to will be better than me trying to explain.. since I have to refer back to a finance-bro meme to replicate it.

But index+match has the same functionality of LOOKUPs but with better flexibility.

Not only is it much more flexible, but way less of a drain on resources.

[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.