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

**DocAElstein** · 09-03-2023, 06:52 PM

This is post https://www.excelfox.com/forum/showt...ll=1#post23285
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D?p=23285&viewfull=1#post23285
https://www.excelfox.com/forum/showt...rr1D#post23285
https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D#post23285

The mathematics in this post are similar to those for the theme of Item way of thinking as alternative to conventional row column looping , although we are finally achieving something more in the opposite direction, we are never the less doing something similar:
In simple words, based on the long string character count (Item number ), we get the required row and column indices.

This post is about:

Use in Application.Index with Look Up Rows and Columns Arguments as VBA Arrays,
arrOut() = Application.Index(arrIn(), rws(), clms()),
https://www.excelforum.com/excel-new...vba-arrays.htm
-
1DarrOut() = Application.Index1D(arrIn(), rws(), clms())
arr2D – arr1D

arr2D() – arr1D() using Index Function
The application will only be described briefly here.
Brief Application explanation
We can explain/ demonstrate ( Examples https://www.excelforum.com/excel-new...vba-arrays.htm
https://www.excelfox.com/forum/showt...ex-application)
https://eileenslounge.com/viewtopic....yDoeky#p265384
https://www.excelfox.com/forum/showt...ll=1#post12072 ) that sometime in Excel and VBA, Excel Index functions when supplied arrays as the second/third row/column argument , ( instead of the more typical single row, and single column pair ) will not error, but rather give an output that has the dimensions of the row and column arrays, and the values as determined by the elements of those row and column arrays.

Excel Spreadsheet example
The full discussion of this is discussed in detail at the other links, but summarised, the macro below will give us something like this

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

What is basically going on is the typical "array type order of working" is done in the typical all columns in row, and then next row, etc., but in this case the value returned as defined by indicia in the row and column arrays are all in a single row which consequently gives the final output in a single row, pseudo like

Rows()

Columns()

a

b

c

d

e

f

=Index( {

a

b

c

, {

1

2

} , {

1

2

3

1

2

3

} )

d

e

f

}

row \ column

1

2

3

1, 1 = a

1 ,2=b

1, 3 = c

2, 1=d

2, 2=e

2, 3= f

1

1, 1 = a

1 ,2=b

1, 3 = c

2

2, 1=d

2, 2=e

2, 3= f

In other words, the row and column locator indicia pairs ( co ordinates) are applied sequentially to the main input array ( which is at the first argument of Index) , so as to give the value at that location.

Code:

Sub Arr1DFromArr2D()  '   https://www.excelfox.com/forum/showthread.php/2789-Item-way-of-thinking-as-alternative-to-conventional-row-column-looping-arr1D-arr2D-arr1D?p=23285&viewfull=1#post23285
Rem 0  Some text in spreadsheet just to help explanation
 Range("A30:Y31").Clear
 Let Range("G30") = " =Index( {": Range("K30") = "      ,   {": Range("K31") = " }": Range("R30") = "  }  ,  {  ": Range("Y30") = "  }  ) "

' arrIn()
 Let Range("H30:J31").FormulaArray = Evaluate("{""a"",""b"",""c"";""d"",""e"",""f""}")
' Row indicies to apply to arrIn()
 Let Range("L30:Q30").FormulaArray = Evaluate("{1,1,1,2,2,2}")
' Column indicies to apply to arrIn()
 Let Range("S30:X30").FormulaArray = Evaluate("{1,2,3,1,2,3}")
' Index formula with array indicies for row and column arguments
 Let Range("A30:F30").FormulaArray = "=INDEX(H30:J31,L30:Q30,S30:X30)"
End Sub

Now here’s the thing
The first post followed an order of doing convention like this,
1 2 3
4 5 6
, then if we looped 1 2 3 4 5 6 , ( the item number )
, then the row of where those things are was 1 1 1 2 2 2 and the column of where those things were was 1 2 3 1 2 3
The first post got us the maths to get those numbers from the item number, 1 2 3 4 5 6
We need Excel "Array" type calculations to get
1 1 1 2 2 2 from 1 2 3 4 5 6
and
1 2 3 1 2 3 from 1 2 3 4 5 6
We are relying on Excel somehow almost doing the loop through 1 2 3 4 5 6 pretty well simultaneously. That is what we really mean by Excel "Array" type calculations

So the main work in this Thread is getting those two required arrays
, for the rows, {1,1,1,2,2,2} and
for the columns, {1,2,3,1,2,3}

If you read the last post, then we can see that each pair of values was got (by an Excel spreadsheet formula, constructed as a string for use in VBA via _ Evaluate(" ")
For the rows Evaluate("INT((" & Ix & "-1)/" & Lc & ")+1")
For the columns Evaluate("" & Ix & "-(INT((" & Ix & "-1)/" & Lc & ")*" & Lc & ")")

They get us a single value in each of the 6 loops,
1 1 1 2 2 2
and
1 2 3 1 2 3
using 1 2 3 4 5 6

We want those all in one go, like arrays

{1,2,3,1,2,3}
and
{1,1,1,2,2,2}

So we will do that in the next post…