Unique Values

There are times when manipulating Unique Values or Items in your database is essential to your analysis.

Counting Unique Values
For instance, let’s say you have a database of everyone who has accessed your website, and you would simply like to know the Number of Unique Users. A clever formula (assumes your data is in A1:A95) does this very efficiently:

        =SUMPRODUCT(1/COUNTIF(A1:A95, A1:A95))

Deleting Duplicates
If you want to know the specific items which are Unique Values, and you don’t mind deleting the duplicates, you can use the Remove Duplicates command in the Data Tools group on the Data tab.

Creating a Separate List of Unique Values
If you wish to retain the original list, and create a separate list of Unique Values, you can turn to the Advanced command on the Sort & Filter group on the Data tab. Select your range and do the following when the menu appears:

1. Select the Copy to another location option

2. Insert the starting cell in the Copy to dialogue box

3. Put a check mark in the Unique records only box

4. Your unique items will appear in the newly-created range

Unique Values can pose Unique Issues but, happily, they are easily solved.

All Dates are Not Created Equal

As anyone who has used our favorite spreadsheet application for a significant amount of time knows, Dates can be occasionally tricky in Excel. As we discussed in 2011, what may Look Like a Date, may not “Play Nice” with other dates that you have in your worksheet.

To paraphrase Gertrude Stein, A Date is a Date is a Date is simply Not true. A good example is a date with an apostrophe at the beginning will look like an ordinary date when entered on a spreadsheet. But it is not…

Let’s say that you are importing records from another database application and you want to perform some Brilliant Analysis (since you are doing this, it must be “brilliant”…) that save the organization countless hours and expense. The trouble is that the dates are saved and exported as Simple Text in the database, and Excel is not giving you the results you deserve. Unless you can rely on the consistency of the way Excel will be handling the “Dates” in the worksheets, you obviously Cannot Rely on the value of your results!

So what is a Brilliant Analyst to do? DATEVALUE to the Rescue! Yes, indeed, my friends, DATEVALUE will calm your jangled nerves, relieve your upset stomach, cure that nagging doubt that you are being watched, bring fast relief for your hangover, and make any Dates in your worksheet work in unison with all of the dates therein. (Your results may vary on some of the attributes listed, but it will make your dates get along with each other…). DATEVALUE will instantly convert any looks-like-a-date Date into the standard Excel serial number, and you can then format it as you wish.

How Totally Rad is that! It may not sound like a big deal, but it can save you a world of grief in many circumstances.

The DATEVALUE function: It’s not going to bring World Peace, but it is Good Stuff to know…

Choose CHOOSE!

Whether you are at a restaurant or working on a spreadsheet, having Choices is almost always a Good Thing.   As we discussed in this blog a couple of years ago, it can be accomplished in Excel by using Nested IF Functions, but they have limitations (e.g. there is a maximum 7 functions allowed in most versions of Excel) and they are cumbersome.

Fortunately, there is a preferable alternative choice to using these awkward IF functions. The CHOOSE function is often a better selection, and it is much more versatile! The CHOOSE function is straightforward and simple to use and is best when combined with other Excel functions. It quite simply returns a value from a list based on a given Position (Index Number).

Here is the Uncomplicated Syntax:

CHOOSE( Index Number, Value1, Value2, ... Value n )

Some Basic Examples:

=CHOOSE(3, “North”, “South”, “Central”, “East”, “West”) returns Central

It also works with ranges:

=Sum(Choose(2, A1:A30, B1:B30, C1:C30) returns the Sum of B1:B30

You can, of course, link it to the value in a cell, making it much more Flexible. For example, you could link it to A1 which contains the Index Number.

If A1 contains the number 4, then =Choose(A1, “North”, “South”, “Central”, “East”, “West”) would return East.

The function can handle up to 29 options, which makes it a great choice in many real-life situations. The CHOOSE function is one of the Undiscovered Treasures in Excel. Give it a try sometime…

Integrity through Validation

Maintaining the integrity of your data is essential in the creation of meaningful reports. The old GIGO adage, “Garbage In, Garbage Out” is as relevant today as it was when an IBM instructor named George Fuechsel first used it 50 years ago.

Using the List option in the Validation tool in Excel is an excellent way of maintaining consistency and integrity within a spreadsheet. Combining it with a Named Range can make it more Effective and Efficient!

Let’s say you are using a dropdown box in a cell that refers to a List of customer service representatives in a large department. For convenience and to assure consistent spelling, you have created the dropdown box using the List feature in Validation.

If the department is large enough, updates to the Validation List will probably be a frequent and tedious task. Rather than having to put up with the hassle of adding/deleting employees from the reference list and then having to adjust the list range, you can simply Name a Range “CSReps”, (or whatever strikes your fancy), and refer to “CSReps” in the List dialogue box in Validation.

Just be sure to allow sufficient room in your CSReps range to add names in the future, and give it an occasional Sort to maintain an Alphabetical Ease-Of-Use.

This is one of those simple techniques that will further establish you as the Excel Guru you truly are.

Happy Excelling All!