Do you know how to use the Unique function in a horizontal data range in Google Sheets? Earlier, we required a helper function. But not now.
Most of the Google Sheets functions work well with vertical tables. But there are exceptions to this.
The Hlookup function in Google Sheets works with a horizontal data set.
Ever tried to use the Unique function in the horizontal data range in Google Sheets? If you try, you can find that it simply returns the data set as it is.
There is a supporting function in Google Sheets that can help you to solve this dilemma. I’ll come to that in the example section below.
Google Sheets Unique Function in Vertical Data Set
Below is an example that shows how the Unique function works in a vertical data set. As you can see, it discards the duplicates and returns unique values.
=unique(A1:B6)The Unique formula works perfectly in similarly arranged data tables.
Google Sheets Unique Function in Horizontal Data Range
The following example shows (the formula after the screenshot) how to use the Unique function in a horizontal data range in Google Sheets.
But the screenshot below shows how not to use Google Sheets’s Unique formula horizontally.
The formula returns the data without any change as it is not the correct use.
=unique(A1:F2)Then what is the proper use of Unique in a horizontal data set?
At the beginning of this tutorial, I mentioned a supporting function that can solve such issues.
Yes! You can use the TRANSPOSE function together with most of the functions to make them support horizontal data sets.
The proper use of the Unique function in the above horizontal data range is as follows.
=transpose(unique(transpose(A1:F2)))Note: You can now use it as follows. The Transpose is not required.
=unique(A1:F2,true)Anatomy of the Transpose-Based Formula:
=transpose(A1:F2)The above formula converts the horizontal data set to a vertical one.
=unique(transpose(A1:F2))This formula removes the duplicates. Now the result is in vertical form.
With another Transpose formula, we can make the data appear horizontally again. That’s all.
This way, you can use the Unique function in the horizontal data range in Google Sheets.
These tips apply to most of the functions in Google Sheets.
Conclusion
Using the Unique function in a horizontal data range in Google Sheets can be tricky. However, by combining it with the TRANSPOSE function, you can easily overcome this challenge. Remember, these tips apply to most of the functions in Google Sheets.
For more Google Sheets tips and tricks, visit Crawlan.com.
