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
