It is 4 because the value Dave is placed in the fourth position of the list.

This tutorial describes multiple ways to extract a unique or distinct list from a column in Excel.

This post also covers a method to remove duplicates from a range.

To make it more clear, unique values are the values that appear in a column only once.

Identify Duplicates In Excel 2016 Download The Excel

Sample File Click on the link below and download the excel file for reference.

We will use this workbook to demonstrate methods to find unique values from a column.

Extract unique values from a column The dataset contains 13 records.

The above methods are explained in detail in the following sections.

Hit CTRL SHIFT ENTER to confirm this formula as its an array formula.

If done correctly, Excel will automatically place curly braces.

After placing curly braces, the above formula would look like this.

And paste it down till the cell D12 (Select the range D3:D12 and press CtrlD).

Suppose there are missing or blank values in your list from which you want to extract unique values.

It is different than the standard ENTER button to enter a formula.

Array formula allows you to process a certain operation on multiple values using a single function.

In other words, we can perform some calculation on more than one value without doing it manually on each cell.

For example, you want to multiply each value by 5 and then sum all of the returned values.from multiplication.

Suppose following values are stored in cell A1:A3 25.

Functioning of Formula: Step by Step Step 1: COUNTIF(D2:D2, B3:B15).

Syntax: COUNTIF(range, condition) It counts the number of cells within a range that meet the given condition.

COUNTIF(D2:D2, B3:B15) returns 1 if D2:D2 is found in B3:B15 else 0.

It is 1 when values of D2 and D3 are found and 0 where it is not found.

It gives the relative position of an item in an array that matches a specified value.

MATCH(0,COUNTIF(D2:D2, B3:B15), 0) returns 4 for the second distinct value.

How To Identify Duplicates In Excel 2016 For Mac