SOLVED

List least recent date from table with dynamic array

Copper Contributor

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. 

 

Screenshot 2023-08-30 152351.png

 

 

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 HansVogelaar (MVP)
Solution

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

least recent date.png

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

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.

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

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

least recent date.png

View solution in original post