Say: There are 2 rows in our worksheet with 2 columns "name", "value". It should not remove the name instead replace the value with 0.
Excel 10 ------ Excel 10
Excel 10 ------ Excel 0
Normally when you apply Remove Duplicates command in Excel, it removes the entire duplicate rows. But sometimes, you want the blank cells to replace the duplicate values, in this situation, the Remove Duplicate command will not work. This article is going to guide you remove duplicates and replace with blank cells in Excel.
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
If there are some duplicate data in a range, the following two formulas will guide you to remove all duplicates and leave blank cells in the range. Please do as follows:
1. In the adjacent blank cell, in the case, in cell D2, enter the formula =IF(A2="","",IF(COUNTIF($A2:A15,A2)=1,A2,"")). See screenshot:
2. Press Enter key, and click cell D2, and then drag the fill handle across to the range that you want to contain this formula. And the remaining values will keep the last item of the duplicates, others have been removed and leave blank cells. See screenshots:
3. As they are formulas, when you want to copy them, please paste as values.
Tip: If you need to replace all the duplicate values with blank cells and only keep the unique values, the following formula can help you: =IF(A2="", "", IF(COUNTIF(A$2:A$15,A2)>1,"",A2)), and then you will get the following results:
Note: You can replace cell references in above two formulas according to your case.
The Select Duplicate & Unique Cells utility of Kutools for Excel can help you quickly select the duplicate values in a range. On the other hand, with this utility, you can also compare two ranges, and select all duplicates in one range at once.
1. Select the range that you want to apply this utility.
2. Click Kutools > Select Tools > Select Duplicate & Unique Cells, see screenshot:
3. In the Select Duplicate & Unique Cells dialog box, select Duplicates (Except 1 st one) or All duplicates (Including 1 st one) as you need. See screenshot:
4. Then click OK, if you select Duplicates (Except 1 st one) option, the duplicate values have been selected which not contain the values appear for the first time. And then press the Delete key to remove them. See screenshots:
5. If you select All duplicates (Including 1 st one) option, all the duplicate values which contain the values appear for the first time, and only leave the unique values. And then press the Delete key to remove them. See screenshots:
Note: Select entire rows: With this option, the entire rows which contain the same values will be selected.