Daily, Excel users have to deal with duplicate entries in datasets. In that case, the Excel feature of Advanced Filter for unique records only is a convenient way out. We can execute advanced filtering that filters only unique or removes duplicates using Excel features, the UNIQUE function (Only in Excel 365) as well as VBA macro.
Let’s say, we have a dataset that contains multiple identical entries. We want to remove identical entries keeping one of them unique.
In this article, we demonstrate several ways to utilize advanced filter for unique records only.
Use Advanced Filter for Unique Records Only in Excel: 4 Ways
Method 1: Excel Advanced Filter Feature to Filter Unique Records
Excel provides an option in the Data tab as Advanced Filter. This Advanced Filter feature can filter unique values only. That means the feature keeps one of the duplicate records as unique and removes the rest.
After inspecting the dataset, we find 3 sets of identical records. Therefore, we have to remove these identical but one of each set remains in the dataset as unique.
Step 1: Select the entire range then, Go to the Data tab > Select Advanced (from the Sort & Filter section).
Step 2: The Advanced Filter window appears. In the window,
Under Action > Mark the Copy to another location option.
The List range is automatically selected (i.e., B4:F17).
Select Copy to location (i.e., H4)
Tick the Unique Records Only option.
Click OK.
➤ Clicking OK places the unique entries in the new location that you provide in the Advanced Filter window’s Copy to option.
🔁 Imposing Criteria to Use Advanced Filter for Unique Records Only
Imposing criteria to filter entries from a range is an easy way to search or find required entries. Suppose we impose criteria regarding Order Date, Product, and Quantity. We want records of products that have a certain amount (>50) of sold Quantity on a certain date (2/3/2022).
➤ Repeat Step 1 of this method afterward the Advanced Filter window appears. Assign options similar to Step 2 except insert the range (i.e., G6:J7) in the Criteria range dialog box. At last, click on OK.
⧬ Make sure you select the Criteria range including column headers.
➤ After clicking OK, Advanced Filter brings records that meet the criteria as depicted in the below picture.
As we have only one record that satisfies the imposed criteria in the dataset, the Advanced Filter feature returns only one record.
Read More: Advanced Filter with Multiple Criteria in Excel
Method 2: UNIQUE Function to Filter Unique Records Only
The UNIQUE function filters unique records only; however, this function is executable only in Excel 365. The syntax of the UNIQUE function is
=UNIQUE (array, [by_col], [exactly_once])In the formula,
array; range or array from where you want the unique values extracted.
[by_col]; extract and compare type. FALSE conduct by Row and TRUE conduct by Column operation. [optional]
[exactly_once]; TRUE refers to a single occurrence value and FALSE refers to all unique values (default).[optional]Step 1: Paste the following formula in any blank cell (i.e., H4).
=UNIQUE(B4:F17)The UNIQUE function only takes the array (i.e., B4:F17) and returns all the uniques.
Step 2: Hit ENTER then in a moment all the unique values appear as shown in the following picture.
From the above screenshot, you can see all the unique records extracted from the dataset.
Read More: How to Use Advanced Filter If Criteria Range Contains Text in Excel
Method 3: Remove Duplicates Feature to Remove Duplicates
Removing duplicates is also one of the convenient ways to filter for unique records. Excel has the Remove Duplicates option in the Data tab. This Remove Duplicates feature keeps one identical record among duplicates.
Step 1: Select the range, then, Go to the Data tab > Select Remove Duplicates (from Data Tools section).
Step 2: The Remove Duplicates window appears. In the window, Click on the Select All option then Click on OK.
Step 3: A notification window appears saying Excel has removed 3 duplicates. Click on OK.
➤ Execution of the Remove Duplicates feature removes duplicates and keeps unique records only.
Read More: Excel Advanced Filter: Apply “Does Not Contain”
Method 4: VBA Macro to Filter Unique Records
VBA Macros are powerful for achieving criteria-based outcomes. We can use macro code to filter unique records only.
Already we have the dataset that contains duplicates. We color format duplicate entries in order to identify them easily.
Step 1: Press ALT+F11 altogether to bring up Microsoft Visual Basic window. In that window, Select Insert (from the Toolbar) > Click on Module.
Step 2: Type the following macro in the Module.
Option Explicit
Sub Filter_Unique_Records()
Dim SourceRng As Range, PasteRng As Range
Dim lastRow As Long
Dim wrk As Worksheet
Set wrk = ThisWorkbook.Sheets("VBA")
Set PasteRng = wrk.Cells(4, 8)
If PasteRng <> vbNullString Then
lastRow = wrk.Columns(PasteRng.Column).Find("*", , , , xlByRows, xlPrevious).Row
wrk.Range(PasteRng, Cells(lastRow, PasteRng.Column + 2)).Delete xlUp
Set PasteRng = wrk.Cells(4, 8)
End If
lastRow = wrk.Columns(2).Find("*", , , , xlByRows, xlPrevious).Row
Set SourceRng = wrk.Range(Cells(4, 2), Cells(lastRow, 6))
SourceRng.AdvancedFilter Action:=xlFilterCopy, copytorange:=PasteRng, Unique:=True
End Sub
The macro starts the source range from row 4 and column 2. And the paste range starts from row 4 and column 8 using the VBA CELL function. It also imposes a condition to delete contents from the paste range using the VBA Range.Delete method. In the end, the macro executes VBA AdvancedFilter Action.
Step 3: Use the F5 key to run the macro after that return to the worksheet. You see all the duplicate records get removed similar to the below picture.
Read More: How to Use Auto Filter and Advanced Filter in Excel
Download Excel Workbook
Conclusion
In this article, we use multiple Excel features, the UNIQUE function, and VBA macro code to filter only unique. Each above-mentioned method has its own convenience according to data type. Hope you find these methods excel in their cause. Comment if you have further inquiries or have anything to add.
