The UNIQUE function, on the other hand, displays the unique data into a new data range, thereby keeping the original data unchanged. In fact, the main difference between the two methods explained in this tutorial is that the ‘ Remove duplicates’ tool works on and changes the original data range. Attempting to do so will result in a #REF error. Note that the UNIQUE function allows you to remove duplicates dynamically, so any changes you make to the original dataset get automatically updated to the output of the function.Īlso, note that you cannot make any changes to the output. You should now see the set of unique records occupy the range from cell D1 to E8. In cell D1, type the following formula:.Here are the steps you need to follow if you want to use the UNIQUE function to remove duplicates from the above dataset: However, you can even enter the UNIQUE formula in a new sheet and display the unique records there. To enable you to easily see the differences between the original and resultant data, we are going to display the result in the same sheet (in a neighboring cell range). Let us work on the same dataset to understand how the UNIQUE function is applied. Using the Unique Function to Find Unique Values in Google Sheets The function will show the result starting from the cell in which you entered the formula.
The range can include either a range of column names or a range of cell references. Here, range is the range of data that you want to extract unique entries from. The syntax for the UNIQUE function is: UNIQUE( range) If, however, you want to retain the original data set, then using the UNIQUE function would be a better option. So when it removes duplicates, it changes the original data. The ‘Remove duplicates’ tool works on the original dataset. Your resultant dataset would then be as follows.įinding Unique Values Using the UNIQUE Function In that case, you would have three duplicate rows removed, since the row containing the name Paul Rodriguez also gets considered as a duplicate. In case you wanted to remove all rows which contain duplicates of the First name, then you could simply uncheck the box next to Column B in the ‘ Remove duplicates’ dialog box (step 5). This is because, when the ‘ Remove duplicates’ tool finds repeating values, it only retains the first occurrence of the value, deleting all others. You should now find rows 5 and 7 removed. You will see a message box giving you details about how many duplicate rows got deleted and how many unique rows have been retained.Right now, since we want to get rid of records where both first and last names are the same, we will leave it as ‘ Select All’. Under ‘ Columns to analyze’, you get the option to select if you want the tool to consider all columns selected, or a selected few columns.If your selection also contains the column headers, then check the box next to the ‘ Data has header row’ option.This will open the ‘Remove duplicates’ dialog box.From the Data menu, select the ‘ Remove Duplicates’ option.In our example, we selected columns A and B by dragging the mouse pointed over their headers. Select the range of data that you want to work on.Using this method, you can remove duplicates with just three clicks. Google Sheets offers a menu option that is dedicated to just this task – removing duplicates to find unique values. Finding Unique Values Using the ‘Remove Duplicates’ Tool You can use the same methods in case you have the data in a single column as well.
#Excel find duplicates between two sheets how to
In this tutorial, I’m showing you how to find unique values when you have multiple columns.