Excel Tips & Tricks ITT

Help Support Talkbeer:

Joined
Nov 15, 2013
Messages
217
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.
 
Joined
Apr 14, 2013
Messages
2,066
Location
Texas
But you could be doing it so much better!
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.
 
Joined
Apr 10, 2013
Messages
9,043
Location
Squat Rack
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.
We will figure it out for you. Hence, this thread.
 
Joined
Sep 15, 2013
Messages
3,140
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):


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!
 
Joined
Apr 10, 2013
Messages
9,043
Location
Squat Rack
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):


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!
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...
 
Joined
Nov 15, 2013
Messages
217
Location
Plano, TX
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):


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!
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.
 
Joined
Apr 10, 2013
Messages
9,043
Location
Squat Rack
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.
Ah, good call. I rarely use subtotals but it works. Pivots are just so damn fast and ridiculously good-looking.
 
Joined
Sep 15, 2013
Messages
3,140
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!
 
Joined
Apr 10, 2013
Messages
9,043
Location
Squat Rack
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!
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

 
Last edited:
2
Top