Excel Tips & Tricks ITT

Discussion in 'Totally Off-Topic' started by Fingolfin, Oct 27, 2014.

Help Support TalkBeer Community by donating:

  1. trickytunadicky

    trickytunadicky

    Joined:
    Jan 30, 2014
    Likes Received:
    87,078
    Location:
    Chicagoish
    I dicked around flipping between 2003 and 2010 trying to fenagle a borked chart again. I hate version migration.
     
    RedCoffee likes this.
  2. RedCoffee

    RedCoffee

    Joined:
    Dec 21, 2013
    Likes Received:
    69,133
    Location:
    FIB
    I can confirm that INDEX MATCH defeats VLOOKUP in most situations. If they ever bitch about my internet activity, this topic is being used as evidence.
     
    tehzachatak, Canti4341 and chaggy5 like this.
  3. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,066
    Location:
    Squat Rack
    Not convinced that INDEX/MATCH meets my needs better than VLOOKUP.

    I regularly have to match thousands of customer IDs across sheets. For example: I get a list of customers IDs on one sheet and need to match ID from another sheet that contains email address. VLOOKUP returns the email for each customer ID match.

    From what I know about INDEX/MATCH, you can only input one value to search, is that correct?
     
    Last edited: Nov 12, 2014
  4. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,066
    Location:
    Squat Rack
    A little more research was needed. Got it.
     
    tehzachatak likes this.
  5. BadJustin

    BadJustin Premium Supporter Contributor

    Joined:
    Oct 2, 2013
    Likes Received:
    149,062
    Location:
    Former Janitor
    someone teach me how to make a fucking Pivot Table, I wanted to stab myself yesterday and stuck it on the backburner. Excel noob for sure. Fawk.
     
  6. GRDave

    GRDave

    Joined:
    Oct 1, 2013
    Likes Received:
    86,532
    Location:
    Canuck living in Grand Rapids, MI
    This place never ceases to amaze me. There's an Excel thread now, I know I know, late to the party. I'm considered an Excel guru where I work, but its mostly because I know how to google. It's not that hard to learn. I have never heard of Index/Match, investigating now.
     
  7. RedCoffee

    RedCoffee

    Joined:
    Dec 21, 2013
    Likes Received:
    69,133
    Location:
    FIB
    That's the one place I still think VLOOKUP wins - when you just need to append a single column to the end of your dataset. It's just simpler and faster to write.

    I usually have to combine multiple columns from the table I'm "joining." So like all of the fields for a mailing address in separate columns. If you use the $ to lock the references correctly, you can just drag the formula around without needing to go back and change the retrieval column for each one.

    The way I mentally envision it, instead of giving it a column number to retrieve, you're directly telling it which column with a cell reference (which is what allows it to be flexible with copying/dragging).
     
    Fingolfin likes this.
  8. RedCoffee

    RedCoffee

    Joined:
    Dec 21, 2013
    Likes Received:
    69,133
    Location:
    FIB
    I just highlight the whole thing and then go to to the Insert ribbon and choose the Pivot Table icon.

    Then it gives you a list of fields on the right and you can drag them down to one of the four quadrants at the bottom (filters/columns/rows/values). From this point best thing to do is just mess with it and see what it does.
     
    BadJustin and Fingolfin like this.
  9. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,066
    Location:
    Squat Rack
    YouTube machine, bro. Srs. Your question is so incredibly vague, hard to answer it better than the nerds on 'Tube.
    So much this.
     
    Lognar and BadJustin like this.
  10. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,066
    Location:
    Squat Rack
    Ctrl+Shift+End minimizes blanks by just selecting fields containing data.
     
    Rempo, Canti4341, quirkzoo and 2 others like this.
  11. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,066
    Location:
    Squat Rack
    I learned how to make it work for me and I actually like it more than VLOOKUP. It's hard not to just start entering the VLOOKUP syntax...
     
  12. quirkzoo

    quirkzoo

    Joined:
    Apr 10, 2013
    Likes Received:
    33,894
    Location:
    The Misty Mountains
    Keyboard shortcuts?

    [​IMG]

    But seriously, I love you. I always deal with charts that have a bunch of holes in the data and I think I actually might use this one.
     
    Fingolfin likes this.
  13. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,066
    Location:
    Squat Rack
    I get so pissed when there are blanks in my pivot and I have to filter them out.
    Ctrl+Shift+Home if you're starting from somewhere within your data field and want to select all between current and home.
     
  14. quirkzoo

    quirkzoo

    Joined:
    Apr 10, 2013
    Likes Received:
    33,894
    Location:
    The Misty Mountains
    I inherited a contact list from my predecessor. Over 2,000 entries, mostly just emails but some with some additional random information. I have tried pairing it down into useful data and this will definitely be helpful.
     
    Fingolfin likes this.
  15. tehzachatak

    tehzachatak

    Joined:
    Jul 24, 2013
    Likes Received:
    38,128
    Location:
    TalkBeer.com
    Once you do it enough, it becomes second nature just like VLOOKUP. It takes longer cause it's more complicated but I finally got there and now I can write index matches before I've even had one cup of coffee
     
    Fingolfin likes this.
  16. Arbitrator

    Arbitrator Not Patrick

    Joined:
    Apr 10, 2013
    Likes Received:
    125,426
    How easy are Pivot Tables, vlookup, etc to pick up? Any good online tutorials?

    Anyone here use R? I'm taking a course on it. Seems pretty similar to Matlab at a superficial level.
     
  17. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,066
    Location:
    Squat Rack
    If I need to learn something, I always check out YouTube first. A quick search produced tons of results of pivot tables, but I haven't seen them so can't recommend.

    Maybe I should make my own....??

    I have no idea how to do that.
     
    quirkzoo likes this.
  18. tehzachatak

    tehzachatak

    Joined:
    Jul 24, 2013
    Likes Received:
    38,128
    Location:
    TalkBeer.com
    Any one individual thing in Excel is pretty damn easy to learn, IMO, especially if you have any kind of technical familiarity at all. I don't have any particular online site i like better than others - if I'm trying to do something and it's not working or it's a new command to me, I just google it and there are usually a ton.

    I use R on and off. Not really at all an expert yet. I'm really a Stata guy at heart, but I would very much like to be competent in R so that I can eventually all my analysis in a piece of software I can have at home for free rather than having to rely on the software my employer pays thousands of dollars a year for...
     
    Arbitrator likes this.
  19. trickytunadicky

    trickytunadicky

    Joined:
    Jan 30, 2014
    Likes Received:
    87,078
    Location:
    Chicagoish
    People who love INDEX MATCH over VLOOKUP, please explain when/how it is useful. I am having a helluva time working it into my excel routine.
     
  20. tehzachatak

    tehzachatak

    Joined:
    Jul 24, 2013
    Likes Received:
    38,128
    Location:
    TalkBeer.com
     
    Fingolfin likes this.