How to Use Advanced Filter for Unique Records Only in Excel

We have a dataset that contains multiple identical entries. We want to remove identical entries and keep one of them.

Use an Advanced Filter for Unique Records Only in Excel: 4 Ways

Method 1 – Excel Advanced Filter Feature to Filter Unique Records

We find 3 sets of identical records.

Steps:

Select the entire range.
Go to the Data tab.
Select Advanced (from the Sort & Filter section).

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.

This places the unique entries in the new location that you provided in the Advanced Filter window’s Copy to option.

Criteria to Use an Advanced Filter for Unique Records Only

We used 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).

Start the Advanced Filter.
Insert the same values as before.
Insert the condition range (i.e., G6:J7) in the Criteria range dialog box.
Click on OK.

⧬ Select the Criteria range including column headers.

The Advanced Filter brings records that meet the criteria as depicted in the below picture.

Method 2 – The UNIQUE Function to Filter Unique Records Only

The UNIQUE function works only in Excel 365. The syntax of the UNIQUE function is:

=UNIQUE (array, [by_col], [exactly_once])

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]

Steps:

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.

Hit Enter.

Method 3 – Use the Remove Duplicates Feature to Remove Duplicates

Steps:

Select the range.
Go to the Data tab.
Select Remove Duplicates (from the Data Tools section).

The Remove Duplicates window appears.

Click on the Select All option, then click on OK.

A notification window appears saying Excel has removed the duplicates.

Click on OK.

This removes duplicates and keeps unique records only.

Method 4 – VBA Macro to Filter Unique Records

We have the dataset that contains duplicates. We will color format duplicate entries in order to identify them easily.

Steps:

Press Alt + F11 to bring up the Microsoft Visual Basic window.
Select Insert (from the Toolbar) and click on Module.

Insert the following code 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. The paste range starts from row 4 and column 8 using the VBA CELL function. The code will delete contents from the paste range using the VBA Range.Delete method. In the end, the macro executes VBA AdvancedFilter Action.