SQL and Excel Guru's

The Lounge is for non-sport-related topics other than politics, war and religion. Order up your favorite beverage, kick back and enjoy the conversation! RATING: PG-13
SQL and Excel Guru's
Thu Oct 10, 2013 4:57 pm
  • Hey .Net,

    There any guru's out there that have some good websites that helped them self-educate?

    Already have the basics for not them down but looking to get more advanced if I can.
    "Go for Broke" - 442nd Japanese-American Battalion

    Shaun Alexander #37 for the HoF - 119 Games 2176 Att 9429 Yards 4.3 YPC 100 TDs (112 TDs total) 2005 MVP

    "YOU POST...TO WIN...thethread." JesterHawk
    User avatar
    sammyc521
    *GOLD SUPPORTER*
    *GOLD SUPPORTER*
     
    Posts: 4218
    Joined: Sat Mar 03, 2007 2:42 pm
    Location: Pacific Ocean


Re: SQL and Excel Guru's
Thu Oct 10, 2013 5:15 pm
  • Like what type of functions?

    I sometime write manuals and procedures for SQL/Access/MSRA/PIVOTS, etc. I don't know of any websites, but I taught myself, so you could too. No doubt. Help function and trial and error got me to expert level, at Microsoft. I'm not attention-whoring, just saying, I teach and tell my employees the same thing.

    If I get some more detail, I could give you a good place to start. And I may have some documentation on hand.

    PM me...forgot to say.
    @ryanadamdavis
    User avatar
    pehawk
    * Report Button *
     
    Posts: 11390
    Joined: Fri Feb 23, 2007 12:08 pm


Re: SQL and Excel Guru's
Thu Oct 10, 2013 6:21 pm
  • So for SQL, I know the basic functionality; Select, From, Where, etc. Once we get into several lines of searching, I end up going through a lot of trial and error. Not the biggest hurdle, just need to keep myself more familiar with it but any tips on how to find out if it'll cause an error before querying would be nice.

    Most of my SQL knowledge comes from this site; http://www.w3schools.com/sql/. It's gotten me the basics and if I used it normally in my work I'm sure it would be more second nature.

    For Excel, I am again mostly self taught based on work-need. So I can create PivotTables from datafields no problem, but I want to be able to select information across different tabs in Excel along with search functionality like VLookUp stuff. I want to be able to cross-reference data so that I can easily assess sales information on a single page pulled from multiple fields/tables/pages.

    Any help is always appreciated.
    "Go for Broke" - 442nd Japanese-American Battalion

    Shaun Alexander #37 for the HoF - 119 Games 2176 Att 9429 Yards 4.3 YPC 100 TDs (112 TDs total) 2005 MVP

    "YOU POST...TO WIN...thethread." JesterHawk
    User avatar
    sammyc521
    *GOLD SUPPORTER*
    *GOLD SUPPORTER*
     
    Posts: 4218
    Joined: Sat Mar 03, 2007 2:42 pm
    Location: Pacific Ocean


Re: SQL and Excel Guru's
Thu Oct 10, 2013 6:36 pm
  • Sounds stupid, but is there any reason why everything needs to be on separate tabs? I take to creating either a database or a master sheet I use, which consolidates all the dat.

    Also, you do know v-lookups?
    @ryanadamdavis
    User avatar
    pehawk
    * Report Button *
     
    Posts: 11390
    Joined: Fri Feb 23, 2007 12:08 pm


Re: SQL and Excel Guru's
Thu Oct 10, 2013 6:42 pm
  • No reason why it would be on separate tabs... I don't know how it will be formatted when it reaches me so I want to be prepared. This is part of a test that I'm taking.

    I do know how to use basic functionality of a VLookup (Entry field, table, column from which you're pulling information, true=variance allowed vs false=no variance allowed).

    PM and I can send you more details.
    "Go for Broke" - 442nd Japanese-American Battalion

    Shaun Alexander #37 for the HoF - 119 Games 2176 Att 9429 Yards 4.3 YPC 100 TDs (112 TDs total) 2005 MVP

    "YOU POST...TO WIN...thethread." JesterHawk
    User avatar
    sammyc521
    *GOLD SUPPORTER*
    *GOLD SUPPORTER*
     
    Posts: 4218
    Joined: Sat Mar 03, 2007 2:42 pm
    Location: Pacific Ocean


Re: SQL and Excel Guru's
Fri Oct 11, 2013 8:59 am
  • This sounds obvious but have you tried Youtube? Some good tutorials up there.

    If you find that you are using multiple tabs i n Excel you probably should consider migrating everything to Access. You don't have to learn SQL if you know Access. You can use their query design interface. But you will have to know how to split everything into tables.
    Image
    User avatar
    rastahawk
    * Just Chillin' *
     
    Posts: 1085
    Joined: Thu Apr 30, 2009 10:51 am
    Location: Los Angeles


Re: SQL and Excel Guru's
Fri Oct 11, 2013 2:51 pm
  • I really recommend Chandoo (http://chandoo.org/wp/) if you're looking to prepare. When I was teaching myself Excel instead of actually working, I used to log in there every day at work and I think what I read there did more to prepare me than anywhere else.

    Nowadays, though, I just Google my questions since someone else has probably had the EXACT same problem. That works pretty well for when you're actually working and just run into a problem.

    I use VLookup 5 days a week and I am pretty good in Excel overall, so if you need help just holler over FB or something.

    Remember that VLookup is like Zoolander, it can't turn left, so make sure your common value is to the left of whatever column you are pulling from. Also, the Trim function is the #1 problem solver when a VLookup returns the dreaded #N/A.

    Shit, not only am I a nerd, but I'm a nerd in the least exciting program ever.
    Sarlacc, on comparing .NET to Soccer: And why not? It's a bunch of people running around in circles, feigning pain, and never scoring.
    Snohomie
    * NET Draft Guru *
     
    Posts: 3593
    Joined: Fri Apr 17, 2009 5:06 pm
    Location: Bellingham, WA


Re: SQL and Excel Guru's
Fri Oct 11, 2013 2:53 pm
  • Trim function? Don't you mean "MID", you hack?

    Excel's exciting...c'mon man! I make all sorts of cool charts, graphs, err, meh., nevermind.
    @ryanadamdavis
    User avatar
    pehawk
    * Report Button *
     
    Posts: 11390
    Joined: Fri Feb 23, 2007 12:08 pm


Re: SQL and Excel Guru's
Fri Oct 11, 2013 3:41 pm
  • pehawk wrote:Trim function? Don't you mean "MID", you hack?

    Excel's exciting...c'mon man! I make all sorts of cool charts, graphs, err, meh., nevermind.


    If you need to use MID, you should know before you do the VLOOKUP. TRIM is great for when people put spaces in their cells, which I usually don't notice until I get the #N/A.

    I make lots of graphs... and I hate them. Give me a table any day of the week. The nice thing about Excel is that it can do a lot of jobs "okay" - I've made resource loaded plans, line of balances, and short term databases out of Excel, even though that isn't really Excel's purpose.
    Sarlacc, on comparing .NET to Soccer: And why not? It's a bunch of people running around in circles, feigning pain, and never scoring.
    Snohomie
    * NET Draft Guru *
     
    Posts: 3593
    Joined: Fri Apr 17, 2009 5:06 pm
    Location: Bellingham, WA


Re: SQL and Excel Guru's
Fri Oct 11, 2013 3:47 pm
  • I still do my databases in Excel. No shame in it.

    I'm sending our boy here a dummy database, with formulas loaded. I think that should get him to an okay level. Hell, just PIVOTS, VLookup and MSRA got me "expert" around MS's campus. Its amazing little, simple things like that are viewed upon as god like.

    The worst is you ftp some nonsense that reports "8" as "00008". I just multiply that BS by 1.
    @ryanadamdavis
    User avatar
    pehawk
    * Report Button *
     
    Posts: 11390
    Joined: Fri Feb 23, 2007 12:08 pm


Re: SQL and Excel Guru's
Fri Oct 11, 2013 7:02 pm
  • Ryan and Kyle, thanks guys. I'll be hitting you up.

    I found this hour-long tutorial about 26-VLookUp features. I knew only about 5 or so them. Little repetitive, but I figure that's what is expected if you're working off excel spreadsheets all day.

    There's even a practice sheet to follow along.

    http://www.youtube.com/watch?v=-hJxIMBbmZY
    "Go for Broke" - 442nd Japanese-American Battalion

    Shaun Alexander #37 for the HoF - 119 Games 2176 Att 9429 Yards 4.3 YPC 100 TDs (112 TDs total) 2005 MVP

    "YOU POST...TO WIN...thethread." JesterHawk
    User avatar
    sammyc521
    *GOLD SUPPORTER*
    *GOLD SUPPORTER*
     
    Posts: 4218
    Joined: Sat Mar 03, 2007 2:42 pm
    Location: Pacific Ocean


Re: SQL and Excel Guru's
Sat Oct 12, 2013 6:26 am
  • I MUST do something for Sammy. He put up with me in his section for a year (2005?). And, I have a tendency to take over sections at games, Sammy was a trooper.

    Good man, that Sammy.
    @ryanadamdavis
    User avatar
    pehawk
    * Report Button *
     
    Posts: 11390
    Joined: Fri Feb 23, 2007 12:08 pm


Re: SQL and Excel Guru's
Sat Oct 12, 2013 8:59 am
  • pehawk wrote:Trim function? Don't you mean "MID", you hack?

    Excel's exciting...c'mon man! I make all sorts of cool charts, graphs, err, meh., nevermind.

    Excel is the ultimate poor mans programming environment. It is amazingly diverse and usable.
    |~=[==~||~==]=~|
    ||Tfs LnD ] [ HAWKS||
    RIP BFS. He was kind of a douche, but he was our kind of a douche.
    User avatar
    ClumsyLurk
    NET Veteran
     
    Posts: 1702
    Joined: Thu Jul 26, 2012 1:08 pm


Re: SQL and Excel Guru's
Sat Oct 12, 2013 9:18 am
  • pehawk wrote:I MUST do something for Sammy. He put up with me in his section for a year (2005?). And, I have a tendency to take over sections at games, Sammy was a trooper.

    Good man, that Sammy.

    You were good luck that year man. We went 8-0 at Home and went to the Superbowl. I was never bothered by that... Hell I'm all for you coming back to 300.
    "Go for Broke" - 442nd Japanese-American Battalion

    Shaun Alexander #37 for the HoF - 119 Games 2176 Att 9429 Yards 4.3 YPC 100 TDs (112 TDs total) 2005 MVP

    "YOU POST...TO WIN...thethread." JesterHawk
    User avatar
    sammyc521
    *GOLD SUPPORTER*
    *GOLD SUPPORTER*
     
    Posts: 4218
    Joined: Sat Mar 03, 2007 2:42 pm
    Location: Pacific Ocean


Re: SQL and Excel Guru's
Sat Oct 26, 2013 2:58 pm
  • Ive been learning SQL for work for the last month and at times it makes my brain cry.
    ETERNAL BLUE FOREVER GREEN
    User avatar
    Siihawk
    NET Bench Warmer
     
    Posts: 48
    Joined: Fri Sep 06, 2013 9:32 am
    Location: Kent Washington


Re: SQL and Excel Guru's
Sat Oct 26, 2013 8:58 pm
  • Siihawk wrote:Ive been learning SQL for work for the last month and at times it makes my brain cry.


    I deal with SQL all the time and it sucks but it's not so bad once you get used to it. What are they making you learn for work or what parts do you struggle with?
    740i
    NET Bench Warmer
     
    Posts: 3
    Joined: Fri Sep 27, 2013 12:50 pm




It is currently Mon Nov 24, 2014 5:04 pm

Please REGISTER to become a member

Return to [ THE NET LOUNGE ]




Information
  • Who is online