2 Dimensional Arrays VBA

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
I would like to be able to loop through arrays to do a test.

I have one big array. It is two dimensional and has 2000 objects. From this array I would like to test every consecutive set of 20 objects to compare to another array. What kind of structure am I looking for here? In other words how can I relate the big array to the smaller test array and how can I step through the big array?

Ideas, resources, etc appreciated!
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,665
Office Version
2010
Platform
Windows
I have one big array. It is two dimensional and has 2000 objects.
I assume one of the dimensions is fixed in size (the number of columns perhaps?)... which one and how many elements does it have?



From this array I would like to test every consecutive set of 20 objects...
Objects? Do you mean array elements? If so, what constitutes 20 of them... is that the number of fixed columns in the two-dimensional array?



...to compare to another array.
You might want to describe this "other array" to us (is it one-dimensional perhaps?).
 

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
The big array is 2 dimensional and has 2000 rows and 2 columns. The number of columns is fixed at 2.

Yes, array elements. Array elements are equal to # of rows.

The other array to which the smaller array within the set of data (the big array (2000,2)) has the same dimensions (20,2). All in all the "other array" is (20,2), the "smaller array" is (20,2), and the "big array" is (2000,2).

I would like to be able to loop through the big array testing the small array against the other array for all consecutive sets of 20 array elements.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,665
Office Version
2010
Platform
Windows
I would like to be able to loop through the big array testing the small array against the other array for all consecutive sets of 20 array elements.
Without knowing what is in each column of the array, I am still unclear what constitutes 20 array elements... how does the second array element factor into this? Below is a general comment I post sometimes and some of it applies to your current question. I would appreciate it if you would factor the points I raise into your response.

A Generalized "Please Note"
--------------------------------------
For future questions you may ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
 

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
The big array itself is defined in the workbook as a range 2000 rows by 2 columns. The small array is 20 rows by 2 columns and the other array is 20 rows by 2 columns. All the arrays are two dimensional, they have 2 columns. the number of "array elements" used in the test is 20. and the number of array elements in the big array is 2000.

The notation I used below is misleading

The other array to which the smaller array within the set of data (the big array (2000,2)) has the same dimensions (20,2). All in all the "other array" is (20,2), the "smaller array" is (20,2), and the "big array" is (2000,2).
sorry for the confusion
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,665
Office Version
2010
Platform
Windows
The big array itself is defined in the workbook as a range 2000 rows by 2 columns. The small array is 20 rows by 2 columns and the other array is 20 rows by 2 columns. All the arrays are two dimensional, they have 2 columns. the number of "array elements" used in the test is 20. and the number of array elements in the big array is 2000.
I am still unsure of your 20 array elements... is that 10 rows by 2 columns worth of data or is it one column of 20 elements? If the latter, which column... the first or the second?

It might also help if you told us what kind of values are in each column.

Also, you seem to now be mentioning three separate arrays (a large one, a small one and another one)... originally I thought you were mentioning only two arrays. Can you clarify this for us and, if there are three arrays, what is being compared to what?
 

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
The 20 elements are in the second column of the array 20 rows by 2 columns. The data type in the first column are dates of the form MM/DD/YYYY and the data type in the second column are numbers with two decimal places.

The big array itself is defined in the workbook as a range 2000 rows by 2 columns. The small array is 20 rows by 2 columns and the other array is 20 rows by 2 columns. All the arrays are two dimensional, they have 2 columns.
to reiterate, the "Big Array" is 2000 rows by 2 columns. The "Small Array" is 20 rows by 2 columns. The "Other Array" (third array) is 20 rows by 2 columns. The "Small Array" is a subset of the "Big Array". The "Other Array" is constant.

I would like to test all "Small Arrays" that belong to the "Big Array" against the "Other Array" which is constant. To describe it differently, for each "Small Array" in "Big Array" test "Small Array" against "Other Array". The "Small Array" is a set of consecutive 2nd column elements and the "Other Array" is a set of 2nd column elements which are constant.

I imagine the iterative process to step by 1 or -1 to redefine the small array after each test is complete and output is generated.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,307
Messages
5,413,695
Members
403,496
Latest member
chamshop

This Week's Hot Topics

Top