Extract First and Last Records - A Symphony of Dynamic Arrays
If you have Sales data for many clients (or patients, or Transactions, or Students…) and you want to extract the First and Last transactions only for each item, how could we do that?
In this tutorial I use Dynamic Array Functions to solve this problem
So, Let's have a look.
Here is the Function I created:
=LET(A,B2:C2,
B,B3:B34,
C,C3:C34,
D,UNIQUE(B),
First,XLOOKUP(D,B,C),
Last,XLOOKUP(D,B,C,,,-1),
VSTACK(A,SORT(HSTACK(VSTACK(D,D),VSTACK(First,Last)))))
Here is an Alternate Function:
=REDUCE(B2:C2,B3:B34,LAMBDA(a,d,
IF(AND(T(OFFSET(d,{1,-1},))=d),
VSTACK(DROP(a,-1),TAKE(a,-1)),VSTACK(a,HSTACK(d,OFFSET(d,,1)))))
########################################################
To book a Corporate Training at Client Site or Remote, and get a quote: www.OfficeInstructor.com
***********************************************************************************
Please support this channel by Subscribing, Liking and Commenting.