Extract Unique Excel Items and Their First Dates from Multiple Columns
Question details
Create a clean list of unique items from several Excel columns and retain the first date associated with each item.

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

Unpivot item columns in Power Query and keep the earliest date per item
Use Power Query for larger or regularly refreshed datasets that need a repeatable cleanup process.
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. Open the workbook in WPS Spreadsheet: Open the Excel file in WPS Office and select the worksheet containing the date and item columns.
- 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. Filter out blank item records: Select the output range and use Data > Filter to exclude blank cells in the Item column.
- 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. 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.

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.




