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)))))
########################################################
***********************************************************************************
Please support this channel by Subscribing, Liking and Commenting.

[IvanCortinas_ES]

Excellent resolution as always friend Nabil. A pleasure to see your developments with the 365 functions. Thank you very much Sir.

[richardhay]

Good. I have used the XLOOKUP Function to search either last to first or first to last many times. BUT if i want the first/last 5 (or the first/last 3 etc) then I use TAKE to do the same job. Like XLOOKUP, TAKE will do the job but with flexibility of the number of records retrieved.

[svenh]

Thank you for your nice descriptions and the very nice introductions!

I already knew the formula =IF(B2<>B1, B2:C2, IF(B2<>B3, B2:C2, "")) which is probably as old as Excel. The formula must be entered on all rows. However, the result occupies as many rows as the original table. I thought it might be possible to build on it and eventually came up with this solution: =FILTER(B2:C36, (B2:B36<>B1:B35) + (B2:B36<>B3:B37)) which utilizes Boolean algebra.

Even this solution, however, assumes that there are at least two rows for each group. If a group is repeated, they will receive separate values. It may be desirable in some cases.

[esamnada]

ماشاء الله تبارك الله ربنا يكرمك استاذنا الكريم
يا ريت لو يكون فيه قناة بالعربيه

[oscarmendez-roca]

Great video and a very interesting solution. I allow me to propose this alternative:
=VSTACK(B2:C2, FILTER(B3:C34, MAP(B3:B34, LAMBDA(b, COUNTIF(B3:b, b)=1))+(B3:B34<>B4:B35)))

[nadermounir]

Excellent explanation 👌 👏 Thank you Nabil :)

[JoseAntonioMorato]

Dear Nabil,
I really liked the video, but I decided to make my contribution to a new solution, displaying the data stacked horizontally:
=LET(Header, {"Customer", "FirstSales", "LastSales"},
Customer, B3:B34, Sales, C3:C34, D, UNIQUE(Customer),
VSTACK(Header, HSTACK(D, XLOOKUP(D, Customer, Sales, , , {1, -1})))) 🤗

[mungunbayar.bat-ochir]

attention: if there were only a single customer, then it would fail.

=LET(
data;A4:B13;
customer;A4:A13;
uni;UNIQUE(customer);


result
)

[KishoreKumar-ofsb]

Can you provide me with the Excel download file?