Jawadul Islam Chowdhury
In Google Sheets, you might sometimes need to work with repetitive entries and remove the unique values. Finding unique data and deleting them one by one is very tedious when working with a large amount of data. In this article, I will show you 2 easy ways in Google Sheets to remove unique values.
2 Suitable Ways to Remove Unique Values in Google Sheets
We will use the following dataset to demonstrate the 2 easy ways to remove unique values in Google Sheets. The dataset includes city names as well as the countries in which they are located. We will now remove the unique values from the dataset.
In both ways, we will use a Filter column to filter the dataset.
1. Using IF and COUNTIF Functions
To remove the unique values from a specific range of data we will use the IF and COUNTIF functions in Google Sheets. Now, suppose we need to remove the data of countries that appeared once in the dataset. Follow the steps below to do this.
📌 Steps:
- Firstly, select cell D5 and insert the following formula.
IF(COUNTIF($C$5:$C$16,C5)=1,0,1)
Formula Breakdown
- COUNTIF($C$5:C16, C5)
The COUNTIF function will first look for the value in the $C$5 cell. Then, it will determine whether the row number of C5 is less than or equal to the row number returned by C5.
- IF(COUNTIF($C$5:C16,C5)=1,0,1)
Finally, the value will be counted if the cell value satisfies the criteria. It will return 1 in this case. If the value in the C5 cell is not 0, the formula will return 1.
- Then, press Enter and the formula will return the value 1.
- Use the fill-handle tool to copy the formula to the following cells.
- Select the entire Filter column and from menubar select Data >> Create a filter.
- A filter icon will be created on the right-hand side of the Filter column header.
- If you click the filter icon a popup box will appear with different options to filter the data. From the options deselect 0 and then press OK.
- Finally, the output will look like the image below, with the rows with unique values removed.
Read More: Use COUNTIF Function to Count Unique Values in Google Sheets
Similar Readings
- Highlight Unique Values in Google Sheets (9 Useful Ways)
- How to Count Unique Values in Multiple Columns in Google Sheets
- Use UNIQUE Function in Google Sheets (5 Applications)
2. Applying ARRAYFORMULA
The ARRAYFORMULA function returns output for a range of cells Instead of a single cell. Using this function, you can quickly and easily find the output while working with a large amount of data. Now, for our case, use the ARRAYFORMULA function to remove the data of countries that appeared once in the dataset. Follow the below steps.
📌 Steps:
- In the beginning select cell D5 in the Filter column and enter the below formula.
=ARRAYFORMULA(IF(C5:C<>"",COUNTIFS(C5:C,C5:C,ROW(C5:C),"<="&ROW(C5:C)),))
Formula Breakdown
- COUNTIFS(C5:C, C5:C, ROW(C5:C), “<=”&ROW(C5:C))
The COUNTIFS function will initially search for the value of cell C5 in the C5:C range. Then it will check whether the ROW(C5:C) or row number of the C5 cell is less than or equal to the row number returned by ROW (C5:C). It will count the value if the cell value meets the conditions. It will return 1 in this case.
- IF(C5:C<>””,COUNTIFS(C5:C,C5:C,ROW(C5:C),”<=”&ROW(C5:C)),)
And finally, IF(C5:C<>””,) will result in 1 if the value in the C5 cell is not zero.
- Here, ARRAYFORMULA is used to get the output values as an array.
- After that hit Enter and the whole Filter column will return output according to the criteria.
- Now, select the entire Filter column and from menubar select Data >> Create a filter.
- A filter will be created in the Filter Click the filter icon and from the filter options select options Filter by condition >> greater than and in the Value or formula box type 1. Finally, press OK.
- Now, the final output will look like the following image.
Read More: How to Find Unique Values in Google Sheets (5 Simple Ways)
How to Remove Duplicate Values in Google Sheets
To remove duplicate values similar functions and techniques used to remove unique values in Google Sheets can be used as well. Using the built-in feature Remove duplicates in Google Sheets is the easiest and time-saving method to remove duplicate values. Suppose now you need to remove the values that appeared more than once in the dataset. Follow the below steps to do this.
📌 Steps:
- First of all, select the whole dataset and from the menu bar select Data >> Data clean-up >> Remove duplicates.
- After that, a pop-up box named Remove duplicates will appear. Select the options according to the following image.
- Another pop-up box will appear. Click OK.
- Finally, the output after removing the duplicates will look like the following image.
Things to Remember
- Insert functions carefully.
- Select ranges and cells properly while using the formulas.
Conclusion
Hopefully, you can now remove unique values in Google Sheets after going throw the article. If you have any queries or suggestions please comment below. Visit the OfficeWheel website for more Google Sheets-related helpful articles.
