Friday, December 31, 2010

Mr Excel & excelisfun Trick 21: Excel 2010 Sparklines (Amazing Cell Charts!!)

Sparklines can prove very useful for comparison of multiple data sets' trending lines as they compare to the next.  See the following Youtube presentation on how to use this new function to Excel 2010: 

Thursday, December 30, 2010

Removing duplicate records with an Excel VBA script

Let’s say you have a set of data where there are known duplicates.  Now, if you had a small listing, you might be able to pick out the duplicates manually.  The problem is, often you are handed much larger sets of data.  Below are two methods that can be used to remove them:
  1. The easiest method is to use the =countif(B:B,B1) function already built into Excel.
    1. B:B is the range you are looking for the duplicate in.
    2. B1 is the value you are referencing in that range.
    3. By inserting a new column A then populating all rows with this formula your duplicates can be found by clicking on data, filter, then filtering for any items greater than 1 in that column.
    4. Highlight all cells and click Edit | Goto | Special | Visible Cells Only as shown here:

    1. Right click, select delete then select entire row and you will be left with only unique records.
    2. Delete the column containing your formulas and you are finished!
  1. If you find yourself doing the above often (or the data set of is too large) your best bet is to create the following script:
    1. From tools select macro and visual basic editor

    1. Creating the following script will deleted your duplicate records automatically:

Tip:  By going into tools, options, calculation and switching to manual you may speed this procedure up dramatically.

You can now run the script anytime you encounter records where column 2 contains the same value more than once!

Wednesday, December 29, 2010

The difference between recorded macros and VBA scripts

Although the difference lies mainly in how the script is being generated your results can be impacted dramatically.

Recorded Macros - When a macro is recorded you will often create a substantial amount of code that is completely unnecessary (ie. scroll downs).  This is often the most simplest method to create automation.  You simply click tools, then macros then record macros as shown here:

You can then follow the steps you normally take and it will record every move you make in Excel.  Once you click the stop button the macro will be available for repeating these exact steps whenever you need!

VBA scripts - Using effective VBA scripts allows you to eliminate unnecessary steps which improves your speed.  This becomes a very important factor as you add more steps, apply to growing data sets, or when multiple functions are involved.  Remember, your computer has to walk through each step in your script so keeping it short is key.