Forum Discussion
Dynamic Arrays - Extend the last logical row value in a 2D array
As always a fabulous solution - actually 2. I'm using the Index as it is actually so fundamentally simple. Not sure why I couldn't get there but added more to my war chest. I now fully get the sequence inside the index to force the rows. And, of course, xmatch in reverse!! Why did I not think of that!!
ANd the error handling then if flags are missing. Very nice and very reusable.
I now get the scan & map combo. I see the use of the new enhanced byrow - I will carry on with the older version for a bit longer until all have it rolled out but it took a few seconds to figure out how it worked as never actually just seen it in use!
So now I fully get it. The map lets you do the logical test with the flag to leave only the last values in the resulting array and then you just do the usual scan function on that using the NAs to reset the rows. That will be a great addition to my library and I can now think of a number of places I could use that kind of combination
Both fab. I may doa performance test sometime to compare them but the usage is not high so won't make much difference. I have 7 sets of these so far and don't notice anything on the calc speed.
Many thanks to you again - and Peter for his input.
A couple of possibilities include:
=LET(
lastCols, BYROW(G6:N8,LAMBDA(r,XMATCH(1,r,,-1))),
lastVals, INDEX(G12:N14,SEQUENCE(ROWS(G12:N14)),lastCols),
IF(G3:N3<=INDEX(G3:N3,,lastCols),G12:N14,lastVals)
)
-OR-
=LET(
isFcst, BYROW(G6:N8*G3:N3,MAX)<G3:N3,
SCAN(0,MAP(isFcst,G12:N14,LAMBDA(f,v,LAMBDA(a,IF(f,a,v)))),LAMBDA(a,λ,λ(a)))
)
However, if any rows in the "flag" array don't contain a flag, additional steps would need to be taken, e.g. using IFNA to handle errors, which I've included in the attached file...
As always a fabulous solution - actually 2. I'm using the Index as it is actually so fundamentally simple. Not sure why I couldn't get there but added more to my war chest. I now fully get the sequence inside the index to force the rows. And, of course, xmatch in reverse!! Why did I not think of that!!
ANd the error handling then if flags are missing. Very nice and very reusable.
I now get the scan & map combo. I see the use of the new enhanced byrow - I will carry on with the older version for a bit longer until all have it rolled out but it took a few seconds to figure out how it worked as never actually just seen it in use!
So now I fully get it. The map lets you do the logical test with the flag to leave only the last values in the resulting array and then you just do the usual scan function on that using the NAs to reset the rows. That will be a great addition to my library and I can now think of a number of places I could use that kind of combination
Both fab. I may doa performance test sometime to compare them but the usage is not high so won't make much difference. I have 7 sets of these so far and don't notice anything on the calc speed.
Many thanks to you again - and Peter for his input.
The new so called "platform upgrade" has broken this forum for me. It doesn't work properly on my cell phone (probably because it's too old/small), and I'm not about to buy a new phone just to keep up with a hobby, nor am I going to keep my laptop running all day just to monitor recent activity. The new threaded conversation view for replies/comments is also horrendous... nobody in their right mind would want to chase down every thread by clicking "Show More" over and over again to find the latest comment or to see all previous replies/suggested solutions. I don't think I'll be hanging out here much anymore. If you need any more help with your current or future projects, please feel free to email me direct. Take care.
David
Shame about that. I have come to value your contributions since you came on the scene. I certainly agree with you that, for the sort of extended technical discussions we have, there is one sort order of importance, namely chronological. Similarly the only filter of relevance is none.
It appears to be taking a step towards the LinkedIn platform which, in turn, appeared to borrow from Facebook. Suddenly everything was driven by likes (AI algorithms applying Artificial Stupidity to garble the logic of a technical discussion).
Mind you, I was surprised to read you manage to contribute using a phone. I need Excel open to test ideas as I go. Maybe part of a generation that lives and breaths by the power of the mouse 🙂.
Will you still use Chandoo?
Regards, Peter
Hey Peter,
I don't think I'll abandon this site altogether. However, the frequency that I'm active will likely be greatly reduced. Maybe I'll get over my initial disgust in a couple weeks, but the method I previously used to monitor recent activity is D.O.A... I can only view/access the 10 most recent threads when browsing on my cell phone (Show More does nothing), and none of the comments appear when I go to any one thread (the only thing I see is the original question, the solution if present, and gigantic icons to share on LinkedIn, Facebook, etc.). I could tell that someone else had responded to this thread, for example, but didn't know who it was or what was said until I fired up my laptop and logged in. I do all of my testing/posting on my laptop but have always used my cell phone to check for new questions/comments from the comfort of my armchair, then switch over to the laptop when I find something interesting/challenging to work on.
I've sampled all of the other major Excel forums but found this one to be the best because of its "community" format. It's not overly competitive, which leads to a lot of great discussions. Mr. Excel's forum is similar in that regards but doesn't allow the sharing of Excel files, and instead pushes everyone to install their "mini-sheet" add-in, which I'm not willing to do. Interestingly, Chandoo's forum seems to be built on the same platform as Mr. Excel's, but allows Excel files as attachments. Unfortunately, though, there's not a lot of activity on his forum (maybe one or two new questions per week). ExcelForum uses an awkward platform (somewhat dated) and is currently dominated by one over-active contributor. Stack Overflow is too competitive for my taste, which leads to a lot of narcissistic behavior and toxic comments; however, I still jump on and post every now and then just to be a thorn in their sides, lol.
The Microsoft Excel Tech Community has the best group of contributors in my opinion, with the greatest mixture of talent and the most welcoming environment. I'm incredibly disappointed with the new platform downgrade. My two main grievances outlined above are just the tip of the iceberg... there seems to be a number of features that have either been changed for the worse or have been removed altogether. I don't know how active I'll be moving forward, but you'll still likely see me around from time to time.
Regards, David
