Imagine yourself in a situation where you need to create a list of all the applications used in different regions. The goal is to create a unique list from multiple columns. How can you achieve that? Don’t worry, I’ll show you how to use Google Sheets to get this unique list in no time!
Using the UNIQUE function
The UNIQUE function is your best friend when you need to create a unique list in Google Sheets. This function returns the unique rows from the provided source range, eliminating duplicates. The rows are returned in the order they first appear in the source range.
UNIQUE function in Google Sheets
To familiarize yourself with the UNIQUE function and see some examples of its usage, check out this link to the UNIQUE function in Google Docs.
Creating a unique list with a single column
If you want to create a unique list from a single column, you can simply use the UNIQUE function like this:
=UNIQUE(A2:A12)
Using values across multiple columns
If your values are spread across a single row (i.e., they are spread across multiple columns), you can use an additional argument in the UNIQUE function to instruct it to “look at” the columns instead of the rows.
=UNIQUE(A2:E2, TRUE)
Using rows and columns simultaneously
Now, you might be wondering if we can provide the UNIQUE function with an array of values.
=UNIQUE(A2:E12)
Unfortunately, the UNIQUE function cannot handle this particular task. It treats all the values in a row as a unique criterion. This means that if we look at the values in row 2, we are trying to find another row with “deRambler, Fightrr, Kryptis, Perino, deRambler”. Since no other row has this same series of applications, row 2 is considered unique among the other rows.
But don’t worry, we have another trick up our sleeve!
Introducing the FLATTEN function
The FLATTEN function allows you to flatten all the values in one or more ranges into a single column. This is exactly what we need to get our unique list!
FLATTEN function in Google Sheets
To learn more about the FLATTEN function and see some examples of its usage, check out this link to the FLATTEN function in Google Docs.
Using FLATTEN to flatten the values
To see how FLATTEN reacts when we give it the same data range as our previous attempt with the UNIQUE function, use the following formula:
=FLATTEN(A2:E12)
Narrowing down the flattened list to be unique
While we now have all the application columns in a single column, we still have duplicates in the result. Fortunately, we can bring the UNIQUE function back into the conversation to help us remove the duplicated values from the list.
=UNIQUE(FLATTEN(A2:E12))
Creating a filtered and distinct list
Let’s say you want to get a distinct list of applications that only come from North America and South America. The FLATTEN function can accept multiple ranges as input to get distinct lists.
=FLATTEN(B2:B12, D2:D12)
To remove duplicates from the fully flattened list of applications, place the FLATTEN function inside a UNIQUE function.
=UNIQUE(FLATTEN(B2:B12, D2:D12))
Removing empty cells
Since empty cells are considered unique entries, we can filter them out using the FILTER function. This function takes the range and filters to exclude empty cells. We do this by setting it to “!=” (not equal to) and using two sets of quotation marks. Two sets of quotation marks mean “empty text” for most computer applications.
=UNIQUE(FILTER(FLATTEN(B2:B12, D2:D12), FLATTEN(B2:B12, D2:D12) != ""))
Sorting the results
For our final step in getting the perfect list of unique values, let’s sort the list in alphabetical order from A to Z.
Put the previous formula inside a SORT function.
=SORT(UNIQUE(FILTER(FLATTEN(B2:B12, D2:D12), FLATTEN(B2:B12, D2:D12) != "")))
If you want the list to be sorted from Z to A, you need to write the formula like this.
=SORT(UNIQUE(FILTER(FLATTEN(B2:B12, D2:D12), FLATTEN(B2:B12, D2:D12) != "")), 1, FALSE)
And voila! Now you know how to create a unique list from multiple columns in Google Sheets. Use these tips to save time and organize your data effectively. If you want to learn more about Google Sheets and other tips, head over to Crawlan.com, where you’ll find a wealth of information to help you optimize your experience with Google Sheets!
Remember, the link to Crawlan.com should be inserted naturally. Enjoy exploring the endless possibilities of Google Sheets and happy organizing!
