Do you want to remove duplicate values in Google Sheets without deleting the corresponding rows? Well, you’re in luck! In this tutorial, I’ll show you two types of formulas that can help you achieve this.
Finding the Right Formula
Before we get into the formulas, let’s clarify the different scenarios you might encounter when removing duplicates. There are two options:
- Remove duplicates but keep the rows: This means you want to remove duplicate values, but still maintain the original rows.
- Remove duplicates and remain in position: In this case, you want to remove duplicate values and have the remaining values shift up to fill the gaps.
It’s important to note that this tutorial falls under data formatting, not data cleaning. So let’s dive in!
Using a Non-Array Formula
To remove duplicate values without deleting the corresponding rows, you can start with a non-array formula. Here’s how:
- In cell C2, use the formula
=COUNTIF($B$2:B2, B2)to count the occurrences of the first item in the range. - Modify the formula to
=IF(COUNTIF($B$2:B2,B2)=1,B2,""). This checks if the count is 1 and returns the name from cell B2, otherwise it returns a blank. - Drag the formula down to apply it to the rest of the cells.
That’s it! You’ve successfully removed duplicates while keeping the rows intact.
Using an Array Formula
If you prefer using an array formula, here’s an alternative approach:
- Clear the range C2:C14 and enter this array formula in cell C2:
=ArrayFormula(COUNTIFS(B2:B14,B2:B14,ROW(B2:B14),"<="&ROW(B2:B14))). - Modify the formula to remove duplicate values and keep the rows in position:
=ArrayFormula(IF(COUNTIFS(B2:B14,B2:B14,ROW(B2:B14),"<="&ROW(B2:B14))=1,B2:B14,"")). - If you want the formula to include future values in the range, add one more IF statement:
=ArrayFormula(IF(LEN(B2:B),IF(COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B))=1,B2:B,""),)).
That’s all there is to it! You’ve now removed duplicate values without deleting the rows using an array formula.
Another Option: Conditional Formatting
There’s one more technique you can consider to remove duplicate values without deleting the rows in Google Sheets. It’s called conditional formatting, and it offers two advantages over the previous methods:
- It avoids the use of a helper column.
- It visually makes the duplicates invisible by changing the cell background and text color, while still allowing data manipulation.
Here’s how to apply conditional formatting to remove duplicates:
- Select the range B2:B14.
- Go to Format > Conditional formatting to open the ‘Conditional format rules’ panel.
- Under the ‘Single color’ tab, scroll down to find ‘Format rules’ and select ‘Custom formula is’.
- Enter the formula
=COUNTIF($B$2:B2,B2)>1. - Below, choose the text color and fill color as white in the ‘Formatting style’ section.
That’s it! You’ve successfully removed duplicate values without deleting the rows using conditional formatting.
Now go ahead and give these methods a try. I hope you find them helpful in cleaning up your data in Google Sheets.
Enjoy your newfound knowledge and happy data organizing!
