Excel Tips & Tricks ITT

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

Help Support TalkBeer Community by donating:

  1. Canti4341

    Canti4341

    Joined:
    Nov 15, 2013
    Likes Received:
    1,304
    Location:
    Plano, TX
    I "learned" how to concatenate files today. And by that I mean I googled it, copied the code into VB and manipulated it until it did what I wanted. Turned 329 one line files (because our outsourced people are the worst) into one big searchable file.

    Learning how to make macros and semi read VB code - just enough to make tweaks to fit my exact needs - is one of the most useful skills I have developed.
     
    silverstreak, Rempo, chaggy5 and 3 others like this.
  2. kmello69

    kmello69

    Joined:
    Apr 14, 2013
    Likes Received:
    11,112
    Location:
    Texas
    Haha I just read this whole thread, and the only thing I've ever used Excel for is to keep track of my beer.

    I have no idea what I'm doing.
     
  3. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,077
    Location:
    Squat Rack
    But you could be doing it so much better!
     
  4. kmello69

    kmello69

    Joined:
    Apr 14, 2013
    Likes Received:
    11,112
    Location:
    Texas
    Probably true. I'm a total idiot with Excel. I was proud of myself for figuring out how to Sum my total bottles #. I'm sure I'm doing unnecessary steps with everything I do, but I have no time to figure this out.
     
    Fingolfin likes this.
  5. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,077
    Location:
    Squat Rack
    We will figure it out for you. Hence, this thread.
     
  6. trickytunadicky

    trickytunadicky

    Joined:
    Jan 30, 2014
    Likes Received:
    87,078
    Location:
    Chicagoish
    So who knows how to make a button that will log into an OBDC connection and download fresh data for a VLOOKUP?
     
  7. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,077
    Location:
    Squat Rack
    wat

    Srs, what is OBDC?
     
  8. pjs234

    pjs234

    Joined:
    Oct 6, 2013
    Likes Received:
    16,217
    Location:
    The land of steady habits
    Open Database Connectivity?
     
  9. trickytunadicky

    trickytunadicky

    Joined:
    Jan 30, 2014
    Likes Received:
    87,078
    Location:
    Chicagoish
    Oracle Based Database Connectivity, I think.

    I have a spreadsheet set up with a magic button. I could just ask the guy who made it.
    [​IMG]
     
  10. trickytunadicky

    trickytunadicky

    Joined:
    Jan 30, 2014
    Likes Received:
    87,078
    Location:
    Chicagoish
  11. Lognar

    Lognar

    Joined:
    Sep 15, 2013
    Likes Received:
    21,662
    Location:
    Chicagoland
    So, my boss has demanded data from me that I cannot get easily and I need to use Excel to parse out the data I have to get the consolidated data that I need. Other than your most basic Excel stuff I'm shit when it comes to Excel and I don't have the time to teach myself today unfortunately. I spent the last hour trying to make VLOOKUP along with SUM work but to no avail. I'm really hoping someone here can point me in the right direction.

    Here's a slice of the data I'm working with (I've got a 1000+ rows):
    [​IMG]

    I've got multiple entries for the same site number in column A and I need one consolidated total of the corresponding column B amounts for each site number. It doesn't sound like it's difficult but I'm banging my head against my desk today on this.

    Thank you for any help Excel Wizards of TalkBeer!
     
    quirkzoo, Treebs and Fingolfin like this.
  12. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,077
    Location:
    Squat Rack
    A pivot table will do the trick for you. Email me the sheet: kwmiles@gmail.com, I'll send back with Pivot done and then find a tutorial so you can do this in the future.

    Teach a man to fish and all.

    EDIT: inb4, "IF functions will work, too." Yeah, but pivot table is so easy...
     
    Photekut, ant880, stxSS07 and 8 others like this.
  13. Canti4341

    Canti4341

    Joined:
    Nov 15, 2013
    Likes Received:
    1,304
    Location:
    Plano, TX
    Pivot table will work - likely the easiest way.

    Another option (just for learnin' sake) is: highlight all your data, go to the Data tab/menu and select Subtotal. It will add a subtotal at the end of each group and a grand total at the bottom. Then you can use the plus/minus bars that will pop up on the left to hide everything but the totals (and copy/paste to a new sheet if you don't want the data behind it). I think Column A will have to be sorted first or it may sort it for you - don't remember off hand.
     
    ant880, Treebs, Lognar and 1 other person like this.
  14. tehzachatak

    tehzachatak

    Joined:
    Jul 24, 2013
    Likes Received:
    38,130
    Location:
    TalkBeer.com
    Yeah, pivot table def fastest
     
    Lognar and Fingolfin like this.
  15. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,077
    Location:
    Squat Rack
    Ah, good call. I rarely use subtotals but it works. Pivots are just so damn fast and ridiculously good-looking.
     
    Lognar and Canti4341 like this.
  16. Lognar

    Lognar

    Joined:
    Sep 15, 2013
    Likes Received:
    21,662
    Location:
    Chicagoland
    Thank you everyone! I appreciate the help. I'll check out the subtotals and I sent Kevin the file as well.

    I also hugely appreciate helping me learn these Excel techniques, KWMiles and Canti4341.

    Thank you again Excel Wizards of Talkbeer!
     
    Fingolfin likes this.
  17. Canti4341

    Canti4341

    Joined:
    Nov 15, 2013
    Likes Received:
    1,304
    Location:
    Plano, TX
    [​IMG]
     
    Rempo, stxSS07, Lognar and 2 others like this.
  18. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,077
    Location:
    Squat Rack
    Just got back from a meeting, will send it over soon. No problem!

    EDIT:
    Short and sweet. Let me know if you need anything else, Lognar

    [​IMG]
     
    Last edited: Nov 11, 2014
    Rau71, Lurkaholic, Canti4341 and 2 others like this.
  19. quirkzoo

    quirkzoo

    Joined:
    Apr 10, 2013
    Likes Received:
    33,895
    Location:
    The Misty Mountains
    Look here folks, the first instance of someone actually getting work done, while wasting time on talkbeer.
     
    Rau71, Lurkaholic, Rempo and 11 others like this.
  20. quirkzoo

    quirkzoo

    Joined:
    Apr 10, 2013
    Likes Received:
    33,895
    Location:
    The Misty Mountains
    Trady
     
    Rempo, Moondog, Fingolfin and 2 others like this.