|
12-20-2018, 10:31 PM | #61 |
Champs!
Join Date: Mar 2004
Location: Kansas City
Casino cash: $5808476
|
Done, and much appreciated it.
__________________
Super Bowl IV, LIV, LVII & LVIII Champions 2020, 2021, 2023, & 2024 AFC Champions 1985 & 2015 Major League Baseball World Series Champions 1980, 1985, 2014, & 2015 Major League Baseball American League Champions 2015 American League Central Division Champs |
Posts: 53,221
|
12-21-2018, 12:56 AM | #62 |
Ain't no relax!
Join Date: Sep 2005
Casino cash: $2048919
|
CONCATENATE! CONCATENATE! CONCATENATE!!!
__________________
|
Posts: 47,639
|
12-21-2018, 01:07 AM | #63 |
In Search of a Life
Join Date: Jan 2010
Location: L.A.
Casino cash: $2734284
|
|
Posts: 27,304
|
12-21-2018, 01:31 AM | #64 |
MVP
Join Date: Oct 2011
Location: Michigan
Casino cash: $3057078
|
Noticed some minor errors on both of our parts and fixed up your worksheet Coach. Sent it back to you.
Posting here just for others to note the changes. Assuming format of cells as displayed in picture, here are the formulas Cell B2: =IF(A2="","",IFERROR(CONCATENATE(TEXT(LEFT(A2,FIND(",",A2)-1),""),", ",TEXT(LEFT(MID(A2,FIND(" ",A2)+1,3),1),"")),"")) Cell C2: =IF(A2="","",IF(IFERROR(MATCH(B2,F:F,0),0)>0,"On Roster","Not On Roster")) Cell D2: =IF(A2="","",IFERROR(ADDRESS(MATCH($B2,$F:$F,0),5),"No Reference")) Cell F2: =IF(E2="","",IFERROR(CONCATENATE(TEXT(LEFT(E2,FIND(",",E2)-1),""),", ",TEXT(LEFT(MID(E2,FIND(" ",E2)+1,3),1),"")),"")) Conditional Format 1: =$C1="On Roster" Applies to: $B:$B Conditional Format 2: =AND(LEN(INDEX($B:$B,MATCH($F1,$B:$B,0)))>0,$F1<>"Short Name")=TRUE Applies to: $F:$F Image: I'll write up a little tutorial for you on what all of that is doing tomorrow night. |
Posts: 12,681
|
12-21-2018, 07:17 AM | #65 |
On Hiatus
Join Date: Aug 2000
Casino cash: $2339700
|
Whenever I am doing an interview with a prospective new associate I ask them to rate their Excel skills.....so many times they say Advanced or Intermediate.
I have an Excel model we use to test their skill set when they advance to the next round of interviews. You can tell their real level of skill set when you ask them to Concatenate....most freeze up or panic. |
Posts: 11,333
|
12-21-2018, 07:31 AM | #66 |
No Keys, No Problem
Join Date: Sep 2000
Location: Denver
Casino cash: $5083136
|
Everyday. vlookup is a good formula to do list comparisons. Sometimes it's difficult to figure out. I recommend playing around with it with some kind of stupid list. You'll have to Google it or use the help for instructions. They can be a bit of a logical problem. But if you want it take common things out of two lists... It's a good tool.
|
Posts: 30,955
|
12-21-2018, 09:42 AM | #67 | |
MVP
Join Date: Oct 2011
Location: Michigan
Casino cash: $3057078
|
Quote:
VLOOKUP requires alot of processing power because it evaluates an entire table at once, which is why some workbooks with large data sets seem to take forever to populate a change. VLOOKUP cannot look left of the lookup value. Adding and deleting columns in VLOOKUP also, often, breaks formulas because of the static nature of calling the number of columns to the right to look for. You have to change the formula from VLOOKUP to HLOOKUP to handle vertical and horizontal matching. INDEX and MATCH have a bit more flexibility and require significantly less processing power because it evaluates a cell reference rather than an entire table. INDEX/MATCH can look anywhere on a sheet: left, right, up, and down. INDEX/MATCH rarely uses static values unless you explicitly tell it to. Most often there is no need to have a static reference value, which means you can add and delete columns and rows with no effect on the outcome, providing you set up the formulas appropriately. Basically, VLOOKUP is a nice beginner's tool but if you want to do more and get more out of Excel, then learning to use other functions like INDEX, MATCH, LEFT, LEN, ADDRESS, INDIRECT and so forth are going to make your life MUCH easier and more rewarding. Those functions used in conjunction with one another make Excel versatile and allows you to use it like a database. Most beginners struggle with logic though, so keeping the formulas straight and knowing what they are doing is extremely important. Yes, you could just as easily set up an SQL database with two tables for the names and use an INNER JOIN function to return a list of matches or a LEFT/RIGHT JOIN to return one of the lists entirely and all the matches in the other list. It wouldn't be terrible at all to set up, especially if you can code an HTML UI parsing an INSERT INTO function, but most people that are working with Excel don't also use or know SQL. Even then you're going to have to end up with adding some more complex statements using wildcards and such given the parameters Coach outlined because the data sets don't exactly match. Anyhow, pick one, learn one, and then move on from there. For the vast majority of people, learning Excel will be much more valuable in day-to-day business. |
|
Posts: 12,681
|
12-22-2018, 09:21 PM | #68 |
MVP
Join Date: Oct 2011
Location: Michigan
Casino cash: $3057078
|
So, as promised, I'll break things down. I've put this in spoilers because if you have a need/want to learn this shit then I give you the option to read on and open up what is a rather lengthy document. If you don't have the need, it's easy to ignore because everything is collapsed down.
Spoiler!
|
Posts: 12,681
|
1 0 |
12-22-2018, 09:41 PM | #69 | |
Let's Get Weird
Join Date: Apr 2013
Location: Austin
Casino cash: $9990098
|
Quote:
Most people will only ever need Excel and that’s fine. Foundational Excel can get the job done most of the time. Dependent on what your needs are, if one can pick up even basic SQL syntax it will make life SO MUCH easier than resorting to typing out wide Excel formulas. Of course, I’m biased coming from a data science perspective and try to minimize my time in Excel. I’d say I’ve used SQL during 90% of my working days over the last 1.5 years. If one has an opportunity (online courses / YouTube / i.e. at my last gig our BI department was giving SQL lessons) definitely take it, well worth it IMO. |
|
Posts: 15,229
|
12-23-2018, 05:49 PM | #70 |
Champs!
Join Date: Mar 2004
Location: Kansas City
Casino cash: $5808476
|
C3HIEF3S and kccrow,
Good stuff gents. If I were to want to learn more about excel, what would be the best practical approach to go in your opinions? Taking a college course? YouTube? I would imagine it depends on the individual, obviously, but from your experiences, is it better to do online/classes over the YouTube, or the other way around?
__________________
Super Bowl IV, LIV, LVII & LVIII Champions 2020, 2021, 2023, & 2024 AFC Champions 1985 & 2015 Major League Baseball World Series Champions 1980, 1985, 2014, & 2015 Major League Baseball American League Champions 2015 American League Central Division Champs |
Posts: 53,221
|
12-23-2018, 05:50 PM | #71 |
Champs!
Join Date: Mar 2004
Location: Kansas City
Casino cash: $5808476
|
Also, isn't there a formula that picks up on words that "sounds" like type of words?
For instance, cat and hat sounds very similar, yes?
__________________
Super Bowl IV, LIV, LVII & LVIII Champions 2020, 2021, 2023, & 2024 AFC Champions 1985 & 2015 Major League Baseball World Series Champions 1980, 1985, 2014, & 2015 Major League Baseball American League Champions 2015 American League Central Division Champs |
Posts: 53,221
|
12-23-2018, 09:49 PM | #72 | |
MVP
Join Date: Oct 2011
Location: Michigan
Casino cash: $3057078
|
Quote:
I never took a college course in Excel. It's just many years of learning it on my own, asking about things I didn't know or looking it up in books or online. Mr. Excel is a great forum to find things on but you could spend some time looking for the answer you seek. As for the specific question on words that rhyme, I'm not aware of any inherent formula to Excel that will accomplish that task. You could use a starter word with wildcards in a VLOOKUP. For instance, you want to find all words that contain at... you'd put VLOOKUP("*"&"at"&"*",$B1:$B$40,0) or some effect where the B1:B40 range would contain the words you want to check. You'd end up showing duplicates but could certainly do a formula for unique values in another range. |
|
Posts: 12,681
|
12-23-2018, 10:49 PM | #73 | |
Let's Get Weird
Join Date: Apr 2013
Location: Austin
Casino cash: $9990098
|
Quote:
YouTube will be very good for beginning guides and introduction to formulas + concepts. I think first it would be best to identify what you are looking to get out of Excel (in the short-term) to start to kind of narrow down on a couple topics or areas to start out with and then build from there. Definitely take some time to just browse around and find tutorials that work for you and your current skillset. When I am looking for programming / Excel .etc content I always have to sift around to find content that is taught in a way that works for me. Once you get some foundations down and start to get into formulas, community forums such as StackOverflow will often come up during syntax troubleshooting. For troubleshooting, Google will always be your best friend. Most of the syntax that I know off the top of my head with most-anything I use for analytics wasn't because it was taught to me in a classroom, but because when I was trying to solve a problem I thought to myself "my life would be so much easier if there was a way that I could do X". Don't be afraid to learn as you go, that was one of my struggles starting out. I'd tread water thinking I didn't know something well enough yet to try it out instead of just doing it and learning much faster in the process. So in a nutshell, find a source to teach you the basics- whether it's YouTube, Google, books,.. whatever works best for you, then find a problem that you want to solve.. something that interests you and just go for it. Give yourself something tangible to get after once you get to a point where you are comfortable with the interface of Excel. For example, my starting point was my love for baseball analytics. Learning the basics of Excel can be a bit of a drag, but learning the basics of Excel while analyzing baseball data? Now that can be fun. May take a bit to get the hang of, but we all started there as well (and I will always be learning) . Here are a couple of videos that can be examples of videos to get you started: Last edited by C3HIEF3S; 12-23-2018 at 10:55 PM.. |
|
Posts: 15,229
|
01-03-2019, 12:25 AM | #74 |
Better than Nelson
Join Date: Mar 2003
Casino cash: $10000400
|
God dammit, I feel like such a ****ing moron. This seems like it should be a basic ****ing task. How do I do this in google sheets?
|
Posts: 17,356
|
01-03-2019, 12:29 AM | #75 |
Politically Incorrect
Join Date: Feb 2009
Location: Scottsdale, AZ
Casino cash: $2541110
|
Google sheets DOES NOT do everything that Excel does. It is also a little complicated at times to do complex formulas or transactions.
__________________
"The only difference between sex for free and sex for money is that sex for free costs you a WHOLE LOT more!" ~Redd Foxx~ "The men who drafted Patrick Mahomes" |
Posts: 50,720
|
|
|