Excel Challenge - Create sequential numbers with a twist (SCAN)
HTML-код
- Опубликовано: 16 июл 2024
- In this video, we show a method to create a sequence of numbers where the sequence has to be paused when it encounters a non-blank entry and continues afterwards. The trick, for me, lies in the SCAN Function
Workbook link:
docs.google.com/spreadsheets/...
00:00 Introduction
00:28 Problem description
00:51 My thought process
02:06 Why a SCAN function may work
02:32 SCAN function example
04:35 SCAN Solution starts
09:18 Solution 2 using SCAN
11:20 Closing thoughts
A continuous source of learning. Excellent logic. Thank you Victor!!!
Thanks Ivan. We keep learning
More Excel magic from you Victor! 👍
Thanks Mark. Just doing my bit for the Excel community
Victor, you are a magician, you are constantly only increasing your level. Just a pleasure, wow!
I do Excel, not Magic 😁😁 Thanks for your feedback. Much appreciated
Thank you for the simple presentation.
I hope to see more Reduce next time!
You are welcome. i have used the REDUCE in some of the previous solutions demonstrated on the channel
I learn so many interesting techniques to solve problems from your channel. Thanks for sharing Victor!
You are welcome. Glad to know it is helping your Excel journey
Victor you never fail to amaze me , I am getting better by watching your content thank you 🙌🏿
Thank you so much for the feedback, i really do appreciate it
Very neat and logical solution.
Thank you, Victor.
Thank you very much. I appreciate the kind words/feedback
Victor... what a challenge ....and the best part is your elegant solution using scan and spilling a 2D array.... Keep these challenges coming. Thank you for posting this amazing challenge!
Thanks Luis for your comments always. Very much appreciated . The lambda helper functions have no issues spilling a 2D array if the input is a 2D array, the challenge is going from a 1D to a 2D array (Makearray and Reduce will rescue you though)
HI Victor... i just replicated your challenge with SCAN.... it worked like a charm.... beautifully. ! No... my only challenge for me is why a+(b="") equals to 1
@@jazzista1967 a is the total at any point, while b is the value being iterated, b is either blank or not blank, so if you do b="", at the point that b is blank, the result is TRUE, whereas it is FALSE for b not being blank. the implication is that a+(b="") would be a+TRUE(when b is blank) which would mean increasing a's value by 1 or a+FALSE(which means a remains the same, when b is not blank), as TRUE/FALSE are 1/0 in this case. Does this help?
YESSSSSSSSSSS... I got it now..... Thanks for explaining so cristal clear!
Great, Glad to help @@jazzista1967
Thank you Victor. Another great video and solution to what I thought was a real problem. It's just knowing that you can select the whole grid and use it with the SCAN function. Brilliant!
Yea Steve, i remember you pointed this out as one of the challenges you would love I review. It does not appear to be hardest, right, once you are familiar with the SCAN function
@@ExcelMoments Yes, you made it look very simple
@@stevereed5776 Maybe it actually is!! 😀 but not at first look
Дядя Витя, это было превосходно!
You are welcome
Wow, bloody awesome
Thanks for your comments!! Much appreciated
Thank you for making us learn from your content.
Its really helpful for excel enthusiast. Best wishes sir!
Thanks Sunny. We are waiting for your Excel channel too as you are up to very great stuff
thanks for the kind and encouraging words sir@@ExcelMoments ...inspired by your work🙏
Genious!
Far from it 😁😁 I just try to share my "little" with the world
🤯🤯🤯🤯
Let me look for an Excel course on Emoiis. i am struggling to decipher this one. 😁😁😁😁