We start with a list of Apps that are used by various Regions.
Our objective is to create a distinct, single-column list of Apps that are derived from the multi-column list above.
Let’s Try the UNIQUE Function
At this point, many users have become acquainted with the UNIQUE function.
The UNIQUE function returns unique rows in the provided source range, discarding duplicates. Rows are returned in the order in which they first appear in the source range.
For a more detailed explanation of the UNIQUE function along with some examples of its use, check out this Google Docs link to the UNIQUE function.
Google Sheets – UNIQUE Function
To demonstrate the process of generating a unique list, let’s start with a single column example.
We write the below UNIQUE function to create a unique list of Apps located in “Europe”.
=UNIQUE(A2:A12)
Using Values Across Columns
If your values are listed in a single row (i.e., spanning multiple columns), you can use an additional argument in the UNIQUE function to tell it to “look” across columns instead of across rows.
=UNIQUE(A2:E2, TRUE)
“Can we use rows and columns at the same time?”
Now let’s see if we can feed the UNIQUE function a matrix of values.
Enter the following formula that uses range A2 through E13 as the source values.
=UNIQUE(A2:E12)We see that it fails to produce the desired result.
The reason this fails is that UNIQUE is treating all the values of a row as a single criterion. In other words, if we see the values on Row 2, we are trying to find another row with “deRambler, Fightrr, Kryptis, Perino, deRambler”.
Because no other row has that same series of Apps, Row 2 is considered to be unique amongst the other rows.
If UNIQUE can’t handle the task, perhaps a different function may be able to.
Featured Course
Google Sheets – The Comprehensive Masterclass
Enter the FLATTEN Function
The FLATTEN function flattens all the values from one or more ranges into a single column.
For a more detailed explanation of the FLATTEN function along with some examples of its use, check out this Google Docs link to the FLATTEN function.
Google Sheets – Flatten Function
To see how FLATTEN would react when we feed it the same data range as our last attempted UNIQUE formula, write the below formula.
=FLATTEN(A2:E12)We see the following result.
Reducing the Flattened List to be Unique
Although we have stacked all the columns of Apps into a single column, we still have duplicates in the result.
Let’s bring the UNIQUE function back into the conversation to assist with removing the duplicate values from the list.
=UNIQUE(FLATTEN(A2:E12) )
Creating a Filtered & Distinct List
Suppose I wanted a distinct list of Apps that are derived only from North America and South America.
The FLATTEN function can accept multiple ranges as input for deriving distinct lists.
=FLATTEN(B2:B12, D2:D12)
Now let’s remove the duplicates of the fully stacked list of Apps by placing the FLATTEN function inside a UNIQUE function.
=UNIQUE(FLATTEN(B2:B12, D2:D12) )
Removing the Blank Cells
Because empty cells are treated as unique entries, we can filter out the blank cells by placing the above formula in a FILTER function.
The FILTER function will accept the range and filter to not include blank cells. This is defined with a “<>” (not equal to) operator and two sets of double quotes. The two sets of double quotes mean “empty text” to most computer applications.
=UNIQUE(FILTER(FLATTEN(B2:B12, D2:D12), FLATTEN(B2:B12, D2:D12) <> “” ) )
NOTE: You could also have written the formula as follows and it would behave in the same manner.
=FILTER(UNIQUE(FLATTEN(B2:B12, D2:D12) ), FLATTEN(B2:B12, D2:D12) <> “” )Sorting the Results
For our final step of making the perfect list of unique values, let’s sort the list alphabetically from A to Z.
Place the previous formula within a SORT function.
=SORT(UNIQUE(FILTER(FLATTEN(B2:B12, D2:D12), FLATTEN(B2:B12, D2:D12) <> “” ) ) )
If you wanted the list to be sorted in Z to A order, you would need to write the formula as follows.
=SORT(UNIQUE(FILTER(FLATTEN(B2:B12, D2:D12), FLATTEN(B2:B12, D2:D12) <> “” ) ) , 1, FALSE)
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
