Function Problems

How to Use TEXTJOIN and FILTER to Return Unique Dates in Excel

Maira Mehtab Sep 30, 2026 868 views

Question details

The user needs to match specific criteria across two sheets and return a comma-separated list of unique dates using Excel functions.

How to Use TEXTJOIN and FILTER to Return Unique Dates in Excel

Product: Excel
Device & OS: not provided
Scenario: Extracting and combining matching unique dates from another sheet into a single cell.
Observed behavior: The goal is to filter dates based on a specific criteria, remove duplicate entries, format them correctly, and join them with commas.

Before you start

Ensure you are using a version of Excel or a spreadsheet application that supports dynamic array functions like FILTER and UNIQUE, such as Microsoft 365 or WPS Office.

Solution 1Recommended

Combine TEXTJOIN, FILTER, and UNIQUE Functions

Use this nested formula to filter the matching dates, remove duplicates, format them, and join them into a single string.

This solution nests several functions to process the data step by step. The FILTER function isolates the target dates, the UNIQUE function removes any duplicate values from that filtered array, the TEXT function formats the date serial numbers into a readable format, and the TEXTJOIN function pieces them together separated by commas.

Select the target cell

Click on the cell where you want the combined list of unique dates to appear.

Enter the nested formula

Type the formula: =TEXTJOIN(", ",TRUE,TEXT(UNIQUE(FILTER(Sheet1!B:B,Sheet1!A:A=Sheet2!A2)),"mm/dd/yyyy"))

Apply the formula

Press Enter to execute the formula. The cell will now display a comma-separated list of the formatted unique dates.

Combine TEXTJOIN, FILTER, and UNIQUE Functions

Troubleshooting #NAME? Error: If you receive a #NAME? error, your version of Excel may not support newer dynamic array functions like FILTER or UNIQUE.

Advanced Spreadsheet Solution

Use WPS Spreadsheet to Process Dynamic Array Formulas

WPS Office fully supports dynamic array functions like TEXTJOIN, FILTER, and UNIQUE, allowing you to easily manage and extract unique data sets. It is a highly compatible and lightweight alternative for all your complex spreadsheet needs.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing your date datasets.
2. Select the output cell: Click on the cell where you want the comma-separated unique dates to be displayed.
3. Input the array formula: Enter the combination formula using TEXTJOIN, FILTER, and UNIQUE exactly as you would in Excel.
4. Calculate the result: Press Enter to instantly calculate and display the formatted list.

Fully supports advanced dynamic array functions like UNIQUE and FILTER.High compatibility with Microsoft Excel (.xlsx) file formats and formulas.Lightweight software with rapid startup and smooth performance for large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #CALC! error?

The #CALC! error usually occurs when the FILTER function finds no matches based on your criteria. You can handle this by adding an 'if_empty' argument to the FILTER function, like so: FILTER(Sheet1!B:B,Sheet1!A:A=Sheet2!A2, "No Match").

Can I change the date format in the TEXT function?

Yes, you can customize the format by modifying the 'mm/dd/yyyy' string in the TEXT function to your preferred layout, such as 'dd-mmm-yyyy' or 'yyyy-mm-dd'.

How does the TRUE argument work in TEXTJOIN?

The TRUE argument tells the TEXTJOIN function to ignore any empty cells within the array, ensuring your final comma-separated list doesn't have multiple consecutive commas if blanks are encountered.

Excel & Data Problems