How to Use Advanced Filter for Unique Records Only in Excel

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.

Advanced Filter Unique Records Only

 In this article, we demonstrate several ways to utilize advanced filter for unique records only.


 Download Excel Workbook


4 Ways to Apply Advanced Filter for Unique Records Only in Excel

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.

advanced filter dataset-Excel Advanced Filter Unique Records Only

Step 1: Select the entire range then, Go to the Data tab > Select Advanced (from the Sort & Filter section).

advanced filter

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.

advanced filter window

➤ Clicking OK places the unique entries in the new location that you provide in the Advanced Filter window’s Copy to option.

advanced filter without criteria


🔁 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).

advanced filter criteria-Excel Advanced Filter Unique Records Only

➤ 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.

advanced filter Criteria

⧬ 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.

unique values satisfy criteria

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 (15 Suitable Examples)


Method 2: UNIQUE Function to Filter Unique Records Only

Excel’s 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.

unique function-Excel Advanced Filter Unique Records Only

Step 2: Hit ENTER then in a moment all the unique values appear as shown in the following picture.

unique function resultFrom the above screenshot, you can see all the unique records extracted from the dataset.

Read More: Excel VBA Examples of Advanced Filter with Criteria (6 Criteria)


Similar Readings:


Method 3: Remove Duplicates Feature to Remove Duplicates

Removing duplicates is also one of the convenient ways to filter for unique. 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).

remove duplicates-Excel Advanced Filter Unique Records Only

Step 2: The Remove Duplicates window appears. In the window, Click on the Select All option then Click on OK.

Remove duplicates window

Step 3: A notification window appears saying Excel has removed 3 duplicates. Click on OK.

NotificatIon window

➤ Execution of the Remove Duplicates feature removes duplicates and keeps unique records only.

Final result

Read More: Excel Advanced Filter: Apply “Does Not Contain” (2 Methods)


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.

vba-Excel Advanced Filter Unique Records Only

Step 1: Press ALT+F11 altogether to bring up Microsoft Visual Basic window. In that window, Select Insert (from the Toolbar) > Click on Module.

Module insertion

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

vba macro

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.

vba result

Read More: Advanced Filter with Criteria Range in Excel (18 Applications)


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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo