Top 5 Oldest/Youngest Customers
Filtering and sorting a dataset allows you to analyse it more effectively by hiding the surplus and putting column data in ascending or descending order.
An Excel Table provides the tools to do this; however, clearing the filter and sort state means your results are lost, so you may want something more permanent.
Formulas give you the flexibility to decide what, where, and how the data gets outputted.
Recently, Microsoft announced several new functions, including TAKE and CHOOSECOLS. Both are currently only available to Office Insiders on the Beta Channel.
𝗡𝗲𝘄 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀
➤ TAKE — returns rows or columns from the start or end of an array.
= 𝚃𝙰𝙺𝙴 ( 𝚊𝚛𝚛𝚊𝚢 , 𝚛𝚘𝚠𝚜, [𝚌𝚘𝚕𝚞𝚖𝚗𝚜] )
➤ CHOOSECOLS — returns specific columns from an array.
= 𝙲𝙷𝙾𝙾𝚂𝙴𝙲𝙾𝙻𝚂 ( 𝚊𝚛𝚛𝚊𝚢 , 𝚌𝚘𝚕_𝚗𝚞𝚖𝟷 , [𝚌𝚘𝚕_𝚗𝚞𝚖𝟸] , … )
𝗘𝘅𝗮𝗺𝗽𝗹𝗲
A table called tblCustomers contains the customer data from which to return the five oldest and youngest customers. Moreover, we are only interested in the First Name, Last Name and DOB columns.
This is where CHOOSECOL comes in handy, as tblCustomers occupies the 𝚊𝚛𝚛𝚊𝚢 argument and each column number is housed in 𝚌𝚘𝚕_𝚗𝚞𝚖𝟷, [𝚌𝚘𝚕_𝚗𝚞𝚖𝟸] and [𝚌𝚘𝚕_𝚗𝚞𝚖𝟹], respectively.
SORT’s [𝚜𝚘𝚛𝚝_𝚒𝚗𝚍𝚎𝚡] contains 3 to sort by the third column (DOB) of the new array. As it arranges in ascending order by default, [𝚜𝚘𝚛𝚝_𝚘𝚛𝚍𝚎𝚛] is omitted for finding the oldest customers, but -1 is necessary for the youngest.
At this stage, the results are sorted but all 20 rows display. Wrap TAKE around the SORT statement and include 5 in 𝚛𝚘𝚠𝚜 to only return the top five.
