Removing Duplicate Values Without Deleting Rows in Google Sheets

Are you tired of dealing with duplicate values in your Google Sheets? Do you want to remove them without deleting the corresponding rows? Well, you’ve come to the right place! In this article, I will show you two powerful methods to get rid of those pesky duplicates while keeping your precious data intact.

Remove Duplicates but Keep the Rows

When it comes to removing duplicate values in Google Sheets, you have two options: using array formulas or non-array formulas. Let’s dive into each method and see how they work their magic.

Non-Array Formula

First, let’s take a look at the non-array formula. It’s simple and straightforward. Here’s what you need to do:

1. In cell C2, type =countif($B$2:B2, B2). This will count the occurrences of the first item in the range $B$2:B2.
2. Modify the formula to =if(countif($B$2:B2,B2)=1,B2,""). The IF logical test checks if the COUNTIF result is 1. If it is, it returns the name from cell B2; otherwise, it returns a blank.
3. Drag the formula down to apply it to the rest of the cells.

That’s it! You have successfully removed the duplicates while preserving the original row positions.

Array Formula

If you prefer a more advanced approach, the array formula is your best bet. Follow these steps to make the magic happen:

1. Clear the range C2:C14 and enter the following array formula in cell C2:

   =ArrayFormula(COUNTIFS(B2:B14,B2:B14,ROW(B2:B14),"<="&ROW(B2:B14)))

2. Now, let’s modify the above array formula to remove the duplicate values and maintain the row positions:

   =ArrayFormula(if(COUNTIFS(B2:B14,B2:B14,ROW(B2:B14),"<="&ROW(B2:B14))=1,B2:B14,""))

3. If you want the formula to include future values in the B2:B 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,""),))

Boom! You’ve successfully removed duplicates using the array formula. It’s a bit more complex but extremely powerful.

Removing Duplicate Values Without Deleting the Rows: Conditional Formatting

If formulas aren’t your cup of tea, worry not! There’s another way to achieve the same result using conditional formatting. Here’s how it works:

1. Select the range B2:B14.
2. Go to Format > Conditional formatting to open the ‘Conditional format rules’ panel.
3. Under the ‘Single color’ tab, scroll down to see ‘Format rules’. Select ‘Custom formula is’.
4. Insert the formula =countif($B$2:B2,B2)>1.
5. Just below, under ‘Formatting style’, choose the text color and fill color as white.

And voilà! The duplicate values are now visually hidden, making your data manipulation even easier.

That’s all there is to it! Now you can confidently remove duplicate values without deleting the corresponding rows in Google Sheets. I hope you found these methods helpful. If you want to learn more about Google Sheets and boost your productivity, be sure to check out Crawlan.com. Stay tuned for more juicy secrets!

Thanks for reading, and happy Sheets-ing!