Forum Discussion

Copper Contributor

Aug 30, 2023

Solved

List least recent date from table with dynamic array

I have a table listing employees and the dates they have worked on a specific team, which I use to generate a roster of who should be selected next for that team. I'm trying to conve...

Sort Names Sample.xlsx13 KB

excel

Formulas and Functions

OliverScheurich
Aug 30, 2023
puretext
=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)

With Office 365 or Excel for the web you can try this formula which uses your MAX(INDEX(... solution.

OliverScheurich

Gold Contributor

Aug 30, 2023

puretext

=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)

With Office 365 or Excel for the web you can try this formula which uses your MAX(INDEX(... solution.

puretext
Copper Contributor
Aug 30, 2023
Upon further review, this solution actually does too much. I need to pull the list of names from a roster and not the historic roles, because the list will feed into an automated labor plan that accounts for critical roles with special training, new hires, and exemptions from certain roles.

I think I can convert this using sortby, but It will take some time to figure out.
puretext
Copper Contributor
Aug 30, 2023
It works! You just saved me probably 2 weeks of banging my head against the wall, using 5 formulas I wasn't familiar with.