In Vlookup you an use the Unique in certain cases to look up a column with multiple unique search keys.Īs you may think, duplicate removal is not the core purpose of this function. You can use Google Sheets Unique function as part of building many complex formulas that including formulas involving Vlookup. So that when the formula removes duplicates, it removes the duplicate rows containing the lowest value in the second column. =SORTN(SORT(A2:B,2,0),9^9,2,1,1)īefore using the range in SORTN, I have sorted the column 2 in descending order using the SORT function. Actually, in cell E1 (please refer to the screenshot above) I have used the formula as below. Just use this number if you want to remove duplicates. If you put 3, the formula would restrict the output to a total of 3 rows. That means you have control over the number of rows to return. The 9^9 element is used to show the number of rows to return. The last number 1 indicates the sort order. It’s the first column in our sample data. The number 1, that is second from the last in the above formula, indicates the column that contains the duplicates. In this formula, you can specify any individual column containing the duplicates. To get a result as shown on the screenshot, you can use one Unique function alternative! It’s none other than the SORTN! =SORTN(A2:B,9^9,2,1,1) The Alternative to the Unique Function in Docs Sheets Here you should think about an alternative solution and there is one such function in Google Sheets. But we want the second column too with the result. If you use only Column A as range, it would remove the row # 6 from the result. But in column B the values are different for both the “A”s. A1: B in Unique, it won’t find any duplicate rows. The Google Sheets Unique function can’t return an output like this. But you should know how Unique treats duplicates. Can I use Unique in an entire data range? In the above example, I have used the Unique formula in a single column. Leaving the real-life example aside, let us move to some interesting tips and tricks using this function. Unique Function Tips and Tricks in Sheets While removing duplicates it produces interesting status in front of you. That means there is a purpose for using Google Sheets Unique function. If you analyze this Unique formula output you can see that the age of the youngest Miss World title winner is 18 and the age of the oldest Miss World title winner is 24. You can sort it using the SORT function together with UNIQUE. The above output keeps the same order from its source. The formula will remove all the duplicates and return the result as below. I am applying a Unique formula in another column to get the unique age of the winners. In this dataset, pay special attention to the fifth column which contains the age of the winners at their crowning year. So that you can understand what I am going to do with the Unique in Google Sheets. It’s actually a complete data imported from Wikipedia using the IMPORTHTML function. I have a table in Google Sheets which contains the details of the Miss World Pageant winners from the past to present. To know this follow the below example. Instead of giving you a fruit data that is common in example formulas, I am giving you a real-life example formula to make you understand the use of Google Sheets Unique function. What is the real role of the Unique function in Google Doc Sheets? Does it use only for duplicate removal? Real-life Examples to Google Sheets Unique Function Just follow the below Unique function formula example to understand this. The Purpose of Unique Function in Docs Sheets:Īs the name suggests, the purpose of the unique function is to return the unique rows from a specified range. In this, the argument “range” is the data range from which you want to extract the unique rows. Unique Function Syntax, Purpose and Usage Tips The Alternative to the Unique Function in Docs Sheets.Unique Function Tips and Tricks in Sheets.Real-life Examples to Google Sheets Unique Function.Unique Function Syntax, Purpose and Usage Tips.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |