You can use the following formula to find unique values in non-adjacent columns in Excel:

=SORT(UNIQUE(FILTER(A1:C11,{1,0,1})))

This particular formula returns the unique combinations of values in the range **A1:C11** for columns A and C only.

Note that the values in the curly brackets indicate which columns to include when finding unique values.

The number **1** specifies to include a column while **0** specifies to exclude it.

For example:

- The first
**1**indicates that column A should be included. - The
**0**indicates that column B should be excluded. - The second
**1**indicates that column C should be included.

The following example shows how to use this formula in practice.

**Example: Find Unique Values in Non-Adjacent Columns in Excel**

Suppose we have the following dataset that contains information about basketball players on various teams:

Suppose we would like to find the unique combinations of teams and positions.

Since the team and position columns are not directly next to each other, we can’t simply use the **UNIQUE** function.

Instead, we must use the following formula:

=SORT(UNIQUE(FILTER(A1:C11,{1,0,1})))

We can type this formula into cell **D2** and then press Enter:

The formula returns every unique combination of team and position.

Note that in this example we only used two non-adjacent columns but you can include as many non-adjacent columns as you’d like by using more **1**‘s and **0**‘s in the formula.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Count Unique Names

Excel: How to Count Unique Values by Group

Excel: How to Count Unique Values Based on Multiple Criteria