I am using a pre-Excel 365 formula to return the last three services a customer received. However, since I now have Excel 365, trying to figure out a dynamic array formula to make this easier to write. Have tried FILTER along with LARGE with no luck. Formula is as follows:
See attached sample Excel workbook.
Please Login or Register to view this content.
Any ideas? Thanks.
If the dates are always in ascending order per id, then tryFormula:
Please Login or Register to view this content.
Try:
=LET(A,A2:D27,B,UNIQUE(INDEX(A,,2)),C,A1:D1,D,4,VSTACK(C,DROP(REDUCE("",B,LAMBDA(x,y,VSTACK(x,TAKE(FILTER(A,INDEX(A,,2)=y),-D)))),1)))
To return a number OTHER than 4, change the bit in RED.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
It can be made a bit simpler:
=LET(A,A2:D27,B,UNIQUE(INDEX(A,,2)),C,A1:D1,D,4,REDUCE(C,B,LAMBDA(x,y,VSTACK(x,TAKE(FILTER(A,INDEX(A,,2)=y),-D)))))
Thanks. These will work
Glad to help & thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks