Forum Discussion
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 convert this roster generator to using dynamic arrays, which are much more simple and calculate faster. But I can't get over this hump: Given a roster of names, how can I create a dynamic array that gives the last date each member was on the team?
The current formula for "last time" is
=MAX(INDEX((D12=$D$4:$L$9)*$D$3:$L$3,))
This is a continuation of my previous question here. I've uploaded an example file. The full original had too much proprietary info.
=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.
3 Replies
=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.
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.
- It works! You just saved me probably 2 weeks of banging my head against the wall, using 5 formulas I wasn't familiar with.
