Item way of thinking as alternative to conventional row column looping.

[DocAElstein]

This is post #6 https://www.excelfox.com/forum/showt...ll=1#post23393
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping?p=23393&viewfull=1#post23393
https://www.excelfox.com/forum/showt...ping#post23393
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping#post23393

---

This follows on from, and is a direct continuation of, the last post. But having a break here is not such a bad idea, as we are going on to do something that can easily know us Humans out of whack and confuse.

Introduction. Some Clarity/ Revision
So to help offset that confusion and summarise the story so far, the story that in total is post #5 and post #6
We are talking about messing with a function that most usually is used like this, pseudo line

c = Index( {a, b    ,   2 , 1 )
            c, d }

but with the difference that we can get more than one result value out, for example with like

{d, c} = Index( {a, b    ,   {2, 2} , {2, 1} )
                 c, d }

In words:
_ in the first case we got the value out of the first Index array argument that was at the co ordinate, 2, 1 , and
_ in the second case we got the values out of the first Index array argument that was at the co ordinates, 2, 2 and 2, 1

Now
_ first off, forget about the first argument array in Index. That is, was, and always will be an array or spreadsheet range, regardless of me and my array things or not. The whole point about what the Index does is to get at things that are in that array ,( grid, range, worksheet , Table, or whatever you want to call it), and, what specifically it gets is based on the co ordinates in the second and third arguments. It is just those row and column arguments that I am messing with
_ secondly, a lot of the last post was talking about how we can make things give us more than the more usual one result via the Excel "array like" calculations. In this post we specifically want to get somehow conveniently the second and third arguments as arrays. Now this is the bit that can confuse. To do that I am further going to use the thing about how we can make things give us more than the more usual one result via the Excel array like calculations. Just because it happens to be a nice convenient way to do it, that’s all. To clarify the point I am trying to make here, consider something different but specifically demonstrating the point I am trying to make.
Say the last post talked about this idea "addition", as opposed to Excel "array like" calculations
6= 1 + 2 + 3
The this post does something like this to get the second and third parts
6= 1 + (1 + 1) + (2 + 1)
So we are like doping an idea twice, that is nested inside that same idea.
In other words, to get the arrays we need in the second and third arguments of Index to get the Excel "array like" calculations we use Excel "array like" calculations.

---

Back to where we where at the end of the last post
We want to get these arrays,
{1,2,3,1,2,3}
and
{1,1,1,2,2,2}
, and we know we can get them, if we looped the values of Ix of 1 2 3 4 5 6 in these sort of formulas
For the rows Evaluate("INT((" & Ix & "-1)/" & Lc & ")+1")
For the columns Evaluate("" & Ix & "-(INT((" & Ix & "-1)/" & Lc & ")*" & Lc & ")")
( ,where Lc is the number of columns in our range or array first argument of Index)

If you have followed and understood the review/ Introduction, then the following may be very obvious. Instead of
Evaluate("INT((" & Ix & "-1)/" & Lc & ")+1")
, we use this
Evaluate("INT(({1,2,3,4,5,6}-1)/" & Lc & ")+1")
, and instead of
Evaluate("" & Ix & "-(INT((" & Ix & "-1)/" & Lc & ")*" & Lc & ")")
, we use this
Evaluate("{1,2,3,4,5,6}-(INT(({1,2,3,4,5,6}-1)/" & Lc & ")*" & Lc & ")")

So, I think it’s fairly easy to see that the VBA thing that was looped 6 times, " & Ix & " is replaced by a {1,2,3,4,5,6}, which will not be looped. That’s the whole point: An array will be returned, in other words, all 6 values in one go will be returned without looping,( at least without looping in the conventional high level computing sense that we might imagine: An array will be returned with all 6 answers in it. That is because we have tapped into the all columns in a row , then next row, updating done for us Human’s every time something is changed, even in a single cell.

That’s it, basically. Well not quite. Just a couple of things:
…. .next post