jagged arrays

Hello esbencito,

This macro works with the data layout that appears in your posted workbook. If the original workbook layout is different then the macro will need to be updated. There is a button on the worksheet "Desired Result" to run the macro.

Please Login or Register  to view this content.

Hello Hello esbencito,

After downloading the workbook from your previous post and reviewing it, the macro I posted will not work and is inadequate for the task. I do have a couple of questions.

1. Which sections besides the "Clusters" will be updated ?
2. Will the output be going to the sheet named "Desired Result" ?

VBA does support jagged arrays. When checking for duplicates it is better to use either a Collection object or Dictionary object. My preference is to use a Dictionary since no additional coding is needed to check if a key already exists or returning a list of all keys or all items. In VBA a jagged array is simply a 1-D array that holds an array in each element. An array can be resized dynamically in VBA but only the last dimension of the array can be changed. Using a Dictionary with large amounts of data allows a quick check for duplicates in a jagged arrays. Using big O notation to compare lookup times, the dictionary is O(1) constant time complexity while just iterating through the primary array to check for duplicates results in O(n) linear time complexity,

Example of Jagged Array

Please Login or Register  to view this content.

Hello esbencito,

I have a few ideas how to approach this problem. It is a bit complex due to it's size and amount of data to process. Execution speed will be a big part of the decision making process along with flexible coding.

Since VBA Variants can be used as arrays, it makes the coding more flexible since an object reference can saved in the array and not just strings and numbers. Using an Object reference saves time, memory, and provides direct access to the object itself. This can preclude the need to save an array of the object's values along with a reference to the object. This is the elegance of object oriented programming.

I might use the Dictionary to store and access the jagged arrays on the worksheets in a recursive fashion. There may be a better approach to updating the sections. But, I need to review the data layout more closely. I'll keep you posted.