SOLVED

# List least recent date from table with dynamic array

Copper Contributor

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

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: List least recent date from table with dynamic array

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

# Re: List least recent date from table with dynamic array

It works! You just saved me probably 2 weeks of banging my head against the wall, using 5 formulas I wasn't familiar with.

# Re: List least recent date from table with dynamic array

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.