The UNIQUE function

You can find more information about the UNIQUE function at Office Support here.

Oftentimes we have a long list of repeating data; company names, customer names, states, email addresses, etc. and we would like a simple list of all the unique items in the data. Luckily, Microsoft created a new dynamic array function named UNIQUE that can do that.

TIP: If you want to reference an individual column in a data table the syntax is: TableName$[$ColumnName$].$ For example, to reference the Company column in Table$1$ it would be Table$1[$Company$].$

In column A$,$ you will find a long list of company names in Table$1.$ To obtain a list containing each unique company, navigate to cell E$2$ and enter $=$UNIQUE to pull up the UNIQUE function. This function takes three arguments: $=$UNIQUE$($Array$,[$By$\_$Column$],[$Occurs$\_$Once$]).$ The first argument, array, is where the data is located, and the last two arguments are TRUE$/$FALSE$.$

In cell E$2,$ use the UNIQUE function to reference the Company column in Table$1$ for the first argument $($array$)$ and then make the last two arguments both FALSE. Which list below matches the results of the UNIQUE function? Select the correct list.

I. Nakatomi Corp II$.$ Stark Industries III. Hooli

Hooli Initech Massive Dynamic

Massive Dynamic Acme Corporation Soylent Corp

Initech Nakatomi Corp Nakatomi Corp

Acme Corporation Soylent Corp Acme Corporation

Stark Industries Massive Dynamic Initech

Soylent Corp Hooli Stark Industries

III

You now have a list of all the companies in the data table. Another great feature of dynamic array functions is that they will respond to new data. For example, if a new company is added to the Company column, the list in E$2$ will update to reflect the new company and spill into one additional cell.

THE SORT function

You can find more information about the SORT function at Office Support here.

The SORT function can do several things but for this graded tutorial we will keep it simple. It sorts. And by default, it behaves how we would use it most of the time; it sorts data in ascending order. The Company List in column E is not sorted alphabetically and for various reasons we would like it to be$.$ Luckily, we can combine the SORT and UNIQUE functions to achieve this result.

First, return to cell E$2$ and in the formula bar, copy the text $($not the equal sign$)$ of the UNIQUE function you created. In cell F$2,$ begin the SORT function and place the entire UNIQUE function you copied inside the sort FUNCTION: $=$SORT$($UNIQUE$(......)).$

Which list below matches the results of the SORT function? Select the correct list.

I. Massive Dynamic II$.$ Acme Corporation III. Stark Industries

Stark Industries Hooli Soylent Corp

Soylent Corp Initech Nakatomi Corp

Hooli Massive Dynamic Massive Dynamic

Initech Nakatomi Corp Initech

Acme Corporation Soylent Corp Hooli

Nakatomi Corp Stark Industries Acme Corporation

II

By nesting UNIQUE inside of SORT, you have created an alphabetical list of unique items that is also dynamic.

DYNAMIC ARRAY errors

Navigate to cell G$5$ under Customer List Sorted and enter anything: your name, the month you were born, your age, doesn't matter.

Now navigate to cell G$2$ and use everything you learned above about UNIQUE and SORT to create a sorted list of unique customers. What happened? What error did you get?

#SPILL!

The results of dynamic array functions flow down and$/$or across from the cell in which they originate. All those cells must be empty and there must be enough room for the results to flow fully. If not, you will get the error you see above.

Delete the content you placed in cell G$5$ and the SORT$($UNIQUE$)$ function in G$2$ should now work properly. Which list below matches the results of the combined SORT$($UNIQUE$)$ function? Select the correct list.

I. Carver, Aubrey II$.$ Pearson, Nicky III. Hughes, Jody

Dawson, Sidney Murphy, Addison Pearson, Nicky

Doyle, Billie Marshall, Sidney Murphy, Addison

Hughes, Jody Hughes, Jody Dawson, Sidney

Marshall, Sidney Doyle, Billie Doyle, Billie

Murphy, Addison Dawson, Sidney Carver, Aubrey

Pearson, Nicky Carver, Aubrey Marshall, Sidney

Williamson, Sidney Williamson, Sidney Williamson, Sidney

I

REFERENCING DYNAMIC ARRAYS

Navigate to cell I$24$ and enter: $=$F$2$#$.$ Notice what happened? Now look at the reference you just entered more carefully. See the pound sign $($#$)$ at the end? That is how you reference a dynamic array function to another cell. Before proceeding, delete that test content from cell I$24$ so it is blank again.

DATA VALIDATION and DYNAMIC ARRAYS

A great use of dynamic arrays is to populate a data validation cell in the form of a dropdown list. Navigate to cell J$1$ then go to the Data tab and click Data Validation. Next set up the Data Validation as shown below and click OK$.$

Note: If you are using Excel Online or Office $365$ for Mac the dialog box may look slightly different, but it functions the same way.

Now when you navigate to cell J$1,$ it should be a data validation dropdown list