Create a Dynamic Array of the Most Recent Date by Employee in WPS Spreadsheets
Question details
The user needs a single dynamic array formula that lists each unique employee and the most recent date they worked on a team from a roster area with employee names and a date header row.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a roster range containing employee names and a corresponding date header row, and wants a dynamic result that automatically returns unique employees with their latest dates.
- Observed behavior
- The expected result is a two-column dynamic roster that updates automatically and sorts the employees by their most recent date in descending order.
Confirm that your WPS Spreadsheets version supports the dynamic array functions UNIQUE, TOCOL, BYROW, HSTACK, and SORT. Also make sure the source data uses a consistent layout with employee names in one range and dates in the matching header row.
Create the Dynamic Roster with UNIQUE, TOCOL, BYROW, HSTACK, and SORT
Use this single formula when you want a spill range that automatically lists each unique employee and their latest date without helper columns or manual refresh.
Place the formula in an empty cell with enough space to spill downward and to the right. The formula scans D4:L9 for employee names, uses D3:L3 as the corresponding date header, and returns a two-column roster sorted by the latest date in descending order.
Click an empty cell where you want the dynamic roster to start, such as N4.
Type =SORT(HSTACK(UNIQUE(TOCOL(D4:L9,3,1)),BYROW(UNIQUE(TOCOL(D4:L9,3,1)),LAMBDA(x,MAX(INDEX((x=D4:L9)*D3:L3,))))),2,1).
Replace D4:L9 with the actual range containing employee names and D3:L3 with the row or range containing the dates. Keep both ranges aligned to the same columns.
Press Enter. WPS Spreadsheets returns a sorted two-column array: employee names and the most recent date for each employee.
Build a Self-Updating Latest-Date Roster in WPS Spreadsheets
Use WPS Spreadsheets' dynamic array functions to combine UNIQUE, TOCOL, BYROW, HSTACK, and SORT into one formula that returns each employee and their latest date and updates automatically when the data changes.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains the employee roster and date headers.
- 2. Select the output cell: Click an empty cell, such as N4, where you want the dynamic roster to appear.
- 3. Enter the formula: Type =SORT(HSTACK(UNIQUE(TOCOL(D4:L9,3,1)),BYROW(UNIQUE(TOCOL(D4:L9,3,1)),LAMBDA(x,MAX(INDEX((x=D4:L9)*D3:L3,))))),2,1) and press Enter.
- 4. Update the range references: Adjust D4:L9 and D3:L3 to match the actual employee range and date header row in your sheet.
- 5. Verify the sorted result: Confirm that the formula returns employee names and their most recent dates sorted by date descending.
Frequently Asked Questions
What does the TOCOL(D4:L9,3,1) part do in this formula?
It scans the roster range D4:L9, ignores blank cells because the second argument is 3, and returns all employee entries in a single column. The UNIQUE function then removes duplicate employee names from that list.
Why is the date header row included as D3:L3?
The formula multiplies the TRUE/FALSE employee match by D3:L3 so that only dates for a specific employee are considered, and MAX returns the most recent date for that employee.
Will the dynamic array formula update automatically when I add employees?
Yes. If your WPS Spreadsheets version supports dynamic arrays, the spill range recalculates when you edit the source range, add new names, or change dates.
What should I do if WPS Spreadsheets returns a #NAME? error?
#NAME? usually means the formula contains a function that is not available in your WPS version. Update WPS Office to a version that supports UNIQUE, TOCOL, BYROW, HSTACK, and SORT, or use a PivotTable as an alternative.
Can I sort the result by employee name instead of by the most recent date?
Yes. Change the SORT formula's sort_index from 2 to 1 and set the sort order to 1 to sort by the employee name column in ascending order.
