How to Replace Duplicates with Blank Cells in Excel
This post will guide you how to remove duplicates and replace with blank cells in Excel 2013/2016. How do I replace the duplicates with blank cells using a formula in Excel.
Table of Contents
1. Replace Duplicates with Blank Cells Using Formula
Assuming that you have a list of data in range B1:C4, and you want to remove duplicate values in this range of cells and replace it with blank cells. How to achieve it. And if you just use the Remove Duplicates command, it will remove all duplicate rows only. So you can use a formula to remove duplicates and replace it with blank cell based on the IF function, and the COUNTIF function.
Just like this:
Type this formula into a blank cell, such as: D1 and press Enter key in your keyboard, and then drag the AutoFill Handle down to Cell F4 to apply this formula.
2. Replace Duplicates with Blank Cells using Remove Duplicates
You can replace duplicates with blank cells in Excel by using Remove Duplicates feature. Just do the following steps:
Step1: Select the range of cells containing duplicates that you want to replace with blank cells.
Step2: Click on the “Data” tab in the Excel ribbon. Click on the “Remove Duplicates” button in the “Data Tools” group.
Step3: In the “Remove Duplicates” dialog box, make sure that all columns are selected.
Step4: Click “OK” to remove the duplicates.
3. Replace Duplicates with Blank Cells with VBA Code
You can also use the VBA code to replace duplicate with blank cells in Excel. it can use the Application.InputBox function to select a range and a destination cell. Just do the following steps:
Step1: Press Alt + F11 to open the Visual Basic Editor.
Step2: In the Visual Basic Editor, select Insert > Module to create a new module.
Step3: Copy and paste the VBA code provided into the new module. Save the workbook as a macro-enabled workbook with a .xlsm extension.
Sub ReplaceDuplicatesWithOne_excelhow() Dim sourceRange As Range Dim destinationCell As Range Dim cell As Range Dim values As Variant Dim i As Long, j As Long Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") 'Select the range to search for duplicates Set sourceRange = Application.InputBox(prompt:="Select range to search for duplicates", Type:=8) 'Select the destination cell to place the result Set destinationCell = Application.InputBox(prompt:="Select destination cell for the result", Type:=8) 'Copy the values of the selected range to an array values = sourceRange.Value 'Loop through the array and add unique values to a dictionary, replacing duplicates with blank cells For i = 1 To UBound(values, 1) For j = 1 To UBound(values, 2) If dict.Exists(values(i, j)) Then values(i, j) = "" Else dict.Add values(i, j), "" End If Next j Next i 'Paste the result to the destination cell destinationCell.Resize(UBound(values, 1), UBound(values, 2)).Value = values End Sub
Step4: Press Alt + F8 to open the Macro dialog box. Select the macro you want to run from the list of macros and click the Run button.
Step5: select the range to search for duplicates.
Step6: select one destination cell to place the result.
Step7: The VBA code will then execute and replace duplicates with blank cells or keep one copy of each duplicate value. The result will be placed in the selected destination cell.
4. Show Only Duplicate Values
If you want to replace all unique values with blank cells and only show duplicates values in Excel, you can use another formula based on the IF function and COUNTIF function. Like this:
Type this formula into a blank cell and then drag the AutoFill Handle down to other cells to apply this formula.
Let’s see the result:
5. Show Only Unique Values
If you want only to show the unique values and remove all duplicate values and replace with blank cells in Excel, you can use the following formula based on the IF and COUNTIF function.
=IF(B1="", "", IF(COUNTIF(B$1:B$4,B1)>1,"",B1))
6. Video: Replace Duplicates with Blank Cells
This video will guide you through the steps on how to replace duplicates with blank cells in Excel using built-in Excel functions or VBA code.
7. Related Functions
- Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])…
- Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
CommentsSo empty here ... leave a comment!