Excel icon

Counting Unique Occurrences in an Excel Spreadsheet

This formula, entered as an array (Ctrl+Shift+Enter) in a single cell, will show you if there any duplicate entries in a given range (in this case, A1:C100). It is wrapped in an IF function to provide a friendly message; a kind of in-cell error handling, if you will. I usually do this when I know others are going to use the formula, to make it easy for them to understand the output. Otherwise it just gives you the number of unique entries which some people may not know what to do with.

Continue Reading »

Excel iconVBA icon

Formatting Zip+4 Codes

If you have a column of zip+4, this macro will remove the suffix. Simply highlight the cells in question. It will skip any zip codes that don't have the suffix. It uses the Left$ string function which is more efficient than the standard Left() and always returns the first 5 characters in the cell.

Continue Reading »

Random Data Generator