In this video I build a step-by-step solution to find all gaps in a sequence of numbers using Excel 365. The result is then combined into a LAMBDA function with optional parameters as well.
Not all functionality is available unless you have a Microsoft 365 subscription.
Download the example workbook at the following link:
https://docs.google.com/spreadsheets/d/1KUocRriamhz_utMZMcsT46KLNdSvvIfY/edit?usp=sharing&ouid=103919554474380297265&rtpof=true&sd=true
▬ Contents of this video ▬▬▬▬▬▬▬▬▬▬
0:00 - Intro
0:19 - Functionality used in this video
0:50 - Intro (continued)
1:32 - Splitting numbers away from text (VALUE, RIGHT, LEN)
4:09 - MIN and MAX with Array references (# references)
5:05 - SEQUENCE of full range of numbers
6:16 - XLOOKUP to look for gaps
7:28 - The case for using XMATCH
9:14 - FILTER to find the exact list of missing numbers
9:58 - LET's build one combined formula now
14:03 - LAMBDA for the win
14:31 - The Advanced Formula Environment
15:31 - Building our LAMBDA called SequenceGaps
16:14 - LAMBDA Parameters
16:52 - [Optional Parameters]
19:25 - Reviewing and syncing my LAMBDA
20:10 - Using our SequenceGaps LAMBDA function
20:45 - Using SequenceGaps embedded in other formulae
21:36 - Changing our start and end (first and last) values
22:20 - Outro
Thanks for watching.
Please LIKE, SUBSCRIBE and leave a COMMENT!
