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?

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

[digger]

Originally Posted by DaFace

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

Sorry...

[Buehler445]

I use that bitch every day.

A simple =(timeout-timein)*24 makes a timesheet with multiple entities a breeze.

I use vlookup in a real simple purchase unit to application unit conversion.

DaFace actually turned me on to the SUBTOTAL function actually allows you total a column after a filter has been applied. I have an incredibly reerunedly convoluted billing spreadsheet that converts a myriad of field activities to whether or not they are billable to a billion different entities.

I should be doing that asshole on a database, but it's been a billion years since I've built one and I don't want to spend the money on access.

I also have a remarkably difficult to set up but remarkably easy to operate conversion from Quickbooks report to banks cashflow statement that is pretty much a series of references.

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

[kccrow]

And oh.. yeah been using this shit every day for as long as I can remember...

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

[MahiMike]

At work I use Excel daily. Pivot tables are the bomb. But at home I'm a Google docs guy.

[kccrow]

Originally Posted by Coach

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?

I put the Verification on the wrong side in the second example... my oops. It's verifying if Column A is in Column D, so if you follow row 5 across you'll see it is. Column G should actually be in Column C.

My assumption is that you're maintaining an overall roster in Column C and you're entering names in Column A to see if they are on the roster. Reference a couple posts above to see the shorter version formatted correctly.

[Coach]

Originally Posted by kccrow

I put the Verification on the wrong side in the second example... my oops. It's verifying if Column A is in Column D, so if you follow row 5 across you'll see it is. Column G should actually be in Column C.

My assumption is that you're maintaining an overall roster in Column C and you're entering names in Column A to see if they are on the roster. Reference a couple posts above to see the shorter version formatted correctly.

Your assumption is correct that Column C is being maintained as a overall roster, while column A will have either new name or a repeat name, albeit may be missing certain keywords as explained earlier.

I just wished it wasn't such a pain in the ass.

[BucEyedPea]

Never.

Well not exactly never but mostly. Only used it a few times from someone's where I had to fill in something.

[kccrow]

Originally Posted by Coach

Your assumption is correct that Column C is being maintained as a overall roster, while column A will have either new name or a repeat name, albeit may be missing certain keywords as explained earlier.

I just wished it wasn't such a pain in the ass.

You can do some sweet shit with macros as well if you want to. For instance, it could check to see if the name exists in the roster list and, if not, add your entry to the list. Gets a bit complex, but Excel can do much, much more than most people realize.

[Coach]

Originally Posted by kccrow

You can do some sweet shit with macros as well if you want to. For instance, it could check to see if the name exists in the roster list and, if not, add your entry to the list. Gets a bit complex, but Excel can do much, much more than most people realize.

Yeah, I just need to make some time for myself to take some Microsoft Excel classes or something to educate myself more on how to do the advanced stuff.

I can do the basic to intermediate stuff, but the stuff you posted earlier, that's way advanced for my knowledge. Yet, you're right, it does do a lot more than people realize, which is why I need to make time for it.

[SithCeNtZ]

Originally Posted by Buehler445

I use that bitch every day.

A simple =(timeout-timein)*24 makes a timesheet with multiple entities a breeze.

I use vlookup in a real simple purchase unit to application unit conversion.

DaFace actually turned me on to the SUBTOTAL function actually allows you total a column after a filter has been applied. I have an incredibly reerunedly convoluted billing spreadsheet that converts a myriad of field activities to whether or not they are billable to a billion different entities.

I should be doing that asshole on a database, but it's been a billion years since I've built one and I don't want to spend the money on access.

I also have a remarkably difficult to set up but remarkably easy to operate conversion from Quickbooks report to banks cashflow statement that is pretty much a series of references.

I don't know anything about your company obviously, but storage and processing power is so cheap now that you could probably build a DB for free in something like AWS on their free tier. You'd have to learn how to use it of course, but don't let cost be a reason you don't do it.

[jjchieffan]

I use a basic Excel spreadsheet for my business. I do service call work on network equipment. I use it to track my jobs, the mileage, the labor, the parts, who the work was for and the date paid. Comes in really handy for taxes. But the only functions that I ever use are the sum and the sort and filter.

[scho63]

What the hell is Excel???