Wednesday, March 27, 2013

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.

No comments: