Data Import & Export

Extract Unique Excel Items and Their First Dates from Multiple Columns

Phi Hung VoPhi Hung Vo Nov 12, 2026 869 views

Question details

Create a clean list of unique items from several Excel columns and retain the first date associated with each item.

Extract Unique Excel Items and Their First Dates from Multiple Columns
Product
Microsoft Excel 365
Device & OS
not provided
Scenario
Items are stored in multiple columns while their related dates are stored in separate columns or rows. The goal is to reshape the data into item-and-date pairs and identify the first date for each item.
Observed behavior
The source layout contains blanks and repeated items, so it must be normalized before unique items and their first dates can be returned.
Before you start

Confirm which columns contain dates and which contain item values, and make sure the date cells are recognized as real Excel dates rather than text. If using a formula, place it in an empty area so the spilled results have room to expand.

Solution 1Recommended

Use a Microsoft 365 dynamic-array formula to reshape and deduplicate the data

Use this option for a one-cell formula that converts a fixed range into item-and-date pairs, removes blank items, and returns unique rows.

This formula is designed for a range where the relevant date and item columns repeat in a consistent pattern. Update the range in the first LET argument to include your complete source table.

1
Select an empty output cell

Click the top-left cell where you want the results to appear, such as G2. Ensure the cells below and to the right are empty for the spilled array.

2
Enter the dynamic-array formula

Type or paste =LET(in,A2:E21,cols,TOROW(HSTACK(SEQUENCE(COLUMNS(in)-2,1,1,0),SEQUENCE(COLUMNS(in)-2,1,3))),twoCols,WRAPROWS(TOROW(CHOOSECOLS(in,cols)),2),UNIQUE(FILTER(twoCols,TAKE(twoCols,,-1)<>0,""))) and press Enter.

3
Adjust the input range

Replace A2:E21 in the formula with the full range containing your date and item data. If the data is an Excel table, replace the range with the appropriate table reference.

4
Sort or reduce results to the first date

If an item can have multiple dates, sort the resulting item-and-date list by the date column from earliest to latest. Then use Data > Remove Duplicates and select the item column only to keep the first occurrence for each item.

Use a Microsoft 365 dynamic-array formula to reshape and deduplicate the data
Formula availability: This method requires Microsoft 365 functions such as LET, TOROW, HSTACK, WRAPROWS, FILTER, and UNIQUE. Use Power Query when your source range changes frequently or contains many columns.
Work with item-and-date lists in WPS Spreadsheet

Create a Unique Item and First-Date List in WPS Spreadsheet

WPS Spreadsheet can help you organize a normalized item-and-date list with formulas and spreadsheet sorting tools. Build a two-column result, remove blank items, sort dates from earliest to latest, and keep one row for each item.

  1. 1. Open the workbook in WPS Spreadsheet: Open the Excel file in WPS Office and select the worksheet containing the date and item columns.
  2. 2. Create an item-and-date output area: Insert headers such as Item and Date in empty columns, then copy or use formulas to assemble item values with their matching dates.
  3. 3. Filter out blank item records: Select the output range and use Data > Filter to exclude blank cells in the Item column.
  4. 4. Sort by date from earliest to latest: Select the two-column output range, then choose Data > Sort and set the Date column to ascending order.
  5. 5. Remove repeated items: With the sorted output selected, use Data > Remove Duplicates and select the Item column so the earliest row for each item is retained.
Organize repeated item entries into a clean item-and-date list.Sort dates in ascending order to identify the first recorded date for each item.Use duplicate-removal tools to retain one entry per item.Open and edit Microsoft Excel .xlsx and .xls workbooks with familiar spreadsheet controls.
QA img-9

Frequently Asked Questions

How do I return only the first date for each repeated item in Excel?

First create a normalized list with one Item column and one Date column, then sort the Date column from oldest to newest. Use Data > Remove Duplicates and select only the Item column; Excel keeps the first sorted row for each item.

Why does the dynamic-array formula return a spill error?

A #SPILL! error means cells in the formula's output area are not empty, merged, or blocked by an Excel table. Clear the obstructing cells or move the formula to an open range.

Can Power Query combine several item columns into one column?

Yes. In Power Query Editor, select the item columns and choose Transform > Unpivot Columns. The resulting Value column contains the item values from all selected columns.

Should I remove duplicates before or after sorting dates?

Sort dates in ascending order before removing duplicates. This ensures that when duplicates are removed based on the item field, the retained record contains the earliest date.

What should I do if dates are treated as text in Power Query?

Select the date column in Power Query Editor, choose Transform > Data Type > Date, and resolve any conversion errors before sorting. Correct date types are required for an accurate earliest-date result.