MS Excel / VBA help thread

Currently bashing away at intermediate R on datacamp.

I like it a lot. Datacamp. It's a good way of testing things out and testing your learning.
The projects look interesting too.

Recovering from a sever case of manflu and can't for the life of me work out how to do this:

• table of transactions with date, cost, etc. AND category
  
• need the total value of each category in another table
  
  

e.g. add all cells in B2:B100 where corresponding cell in E# = "fruit"

Sorry if that makes no sense!

EDIT:

What was trying to do is:

=sumif(F2:F200,"=cell#",D2:D200)

...but had the F and D cells the wrong way round :S

Yep, that one is always fun, love excel for that, if you use a sumifS it is the other way round, so I tend to just use sumifs even with one criteria as then don't have to fiddle with it if I want to add further criteria later on (also works for averageif).

Would a pivot have solved this?

Could certainly be done with a pivot, but then you have to remember to refresh the pivot each time, or code something to do it for you...

@hugo7 what @Chalfie said, sounds like a Pivot would sort this.

Some people, when confronted with a problem, think "I know, I'll use regular expressions pivot tables." Now they have two problems.

I get totally lost in pivots :(

Even if you put the data in a table?

I'm a fan of writing stuff as then you know what you're counting and why.
Again, tables make this easier.

Good question, potentially, I tend to avoid pivot tables as I like to be able to trace formulas, but I am weird.

Could also set pivot table to autorefresh on opening which would probably help a lot too.

No I think being able to account for your counts is correct.
I didn't know if you wanted a quick answer.

pivot

This has literally changed my life. (along with discovering you can import data from a webpage).

I feel like I may need to take some sort of online xls. course as I'm clearly overusing formulas.

If anyone knows of a particularly good series on YouTube, etc. please let me know.

EDIT: fwiw I'm actually using LibreOffice Calc, which seems to be pretty similar.

I feel like pivots are a kind of anti pattern in excel - though that's a personal observation and I suspect not widespread. As chalfie says above, you can't audit a pivot, and it's very easy to use stale data, or have a super complex pivot hide some data inadvertently.

That said, I've seen some wizards do stuff with them much quicker than I could a formula...

You can build a load of formula to maintain data quality around timeliness, completeness, accuracy etc... and not compromise the point of the pivot as a tool (which is to be cheaper than multiple formula).

That being said, if audit and robustness are requirements, ditch excel altogether.

I'd much rather a decent relational database and a few stored procedures.

Does anyone on here use Tableau? I've started dipping my toe in, and for someone with Excel experience it looks ace.

Pivot tables are my go-to tool in Excel but a lot of people seem scared by them. Excel is branching out as the default tool for data manipulation and pivot tables seem to be an integral part of that. I was happily doing pivots on 90,000,000+ lines of data the other day which wouldn't have been possible till recently.

It’s pretty great but some stuff that’s really easy in Excel is impossible in Tableau. Prepping the data specifically for use in Tableau is also mandatory otherwise manipulating it in Tableau is really difficult.

I recommend watching the tutorial vids on the Tableau website.

Cheers, I've started with those. There's also a series by this guy https://www.udemy.com/tableau10/ that's been recommended. He does other web courses for R, seems like an engaging speaker.

Pivot tables are good for on the fly analysis and grouping but I would never use them in automation or building a model in Excel. I feel like too many people learn how to use Pivot tables and think they’ve learned everything Excel has to offer but there’s so much more.

With the way pivot tables are going now with slicers and timelines and the like I think there is more expectation that they'll be used in models.

Using them in VBA is still a ballache though.

I discovered this week that if you are less proficient at Tableau than I am, know less about SQL and have no analytical skills, but label yourself a Data Visualisation Analyst and claim to be a Tableau guru, you can get paid £550/day.

Can you tell I’m bitter? The person who hired her also stole data I spent months putting together to give to her to create one of the shittest Tableau dashboards I've ever seen.

Fucking hell.

Shall we start a company?

I'm in.

Using them in VBA is still a ballache though.

I’ve cheated in the past and used normal cells to mirror the pivot. Not sure how efficient with 90m lines though!

Yes please. My work just bought Datacamp for everyone on the team so I've just got to go through all the courses on R first to make sure I'm not missing anything and then collect my bonus in my March paycheck first.