Found in the majority of these tips and resources, you will see that I normally avoid VBA and ARRAY formulas like the plague for several good reasons:

  • My clients don’t like it, don’t use it and/or don’t want it.
  • They are more difficult to edit and audit.
  • They create a level of complexity that is normally not necessary.
  • In the vast majority of cases, there is almost always a basic FORMULA solution and/or a simple workaround.
  • I’m just not that really good at using them.

However, in some cases, I have come across VBA coding or an ARRAY formula so helpful and/or the solution is simply not possible without their usage, that I am including it here.

Additionally, I have never found myself using PivotTables for several key reasons.

PivotTables are:

  • Cumbersome
  • Memory hungry
  • Unstable
  • Un-auditable, and;
  • Far too easy to get wrong

Alternatives for analyzing datasets include (but are not limited to): SUMIF, AVERAGEIF, COUNTIF, Lookups, INDEX and MATCH to name the few simple ones.