How to Assign Sequential Numbers to Rows with Matching Dates in Excel
Question details
The user needs a method to automatically assign the same sequential number or rank to rows that share the same date, ordered chronologically.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing datasets where rows with matching dates require identical sequential IDs, from earliest to latest.
- Observed behavior
- The goal is to ensure the earliest date receives 1, the next chronological date receives 2, and identical dates share the exact same assigned value.
Ensure your date column contains valid date values rather than text strings so that formulas and Power Query can evaluate and sort them chronologically.
Use the XMATCH and UNIQUE Dynamic Array Formula
A straightforward dynamic array formula that ranks dates chronologically, assigning identical ranks to duplicate dates.
This formula extracts unique dates, sorts them chronologically, and then finds the match index for each date in your main list. For advanced performance on larger datasets (up to 5,000 rows), the LET function can be combined to avoid recalculation.
Click on the first cell in an empty adjacent column where you want the sequential numbers to appear.
Type `=XMATCH(A:A, SORT(UNIQUE(A:A)))` (replace A:A with your actual date range, such as A2:A100).
Press Enter. The dynamic array formula will automatically spill down the column, assigning accurate sequential numbers to your dates.
Use Power Query for Large Datasets
Ideal for repeatable workflows and large datasets exceeding 5,000 rows without causing lag.
Assign Sequential Numbers Easily with WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced formulas and large dataset management. You can quickly assign identical sequence numbers to matching dates completely free.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your date records.
- 2. Sort and apply a standard formula: Sort your data by the date column chronologically. In cell B2 (assuming dates are in A), type `=IF(A2=A1, B1, B1+1)` where B1 is set to 1.
- 3. Drag to fill: Double-click the fill handle in the bottom-right corner of cell B2 to apply the sequence calculation to the rest of your data.
Frequently Asked Questions
What if my dates are completely out of order?
The SORT and UNIQUE formula combination automatically handles unsorted dates by determining the chronological order internally before assigning the rank, so your source data does not need to be manually sorted.
Why use the LET function instead of just XMATCH?
The LET function optimizes performance in large datasets. It defines variables (like the unique sorted date list) once in memory rather than recalculating the entire array for every single row, preventing the spreadsheet from slowing down.
Can I output my Power Query results to a different worksheet?
Yes. When you choose 'Close & Load To...' in Power Query, you can select 'Existing Worksheet' and pick a specific cell on another sheet, or choose 'New Worksheet' to keep your result table separate from your input data.
