Join large Arrays

[matrex]

Hi,

On Sheet1 all 1048576 rows are filled with data
On Sheet2 the data extends to row 500000

Want to create an array that has the combined data from both sheets (preferrably without using loops as that will take a long time to process)

Idially, creating two arrays
Arr1 = Sheet(1).Range("A1:A1048576")
Arr2 = Sheet(2).Range("A1:A500000")

then somehow joining them into a single array

The problem is I am not able to make the Union command to work accross 2 sheets

Thanks

[Steffen Thomsen]

I think the best/fastest way is to use a third sheet.

Copy data to this sheet in same column and then set the array from there.
Else you need to loop.

[XLent]

Steffen Thomsen given the sample that would not work (rows exceeded)

Iterating Arrays is very fast. Avoid use of Preserve.

The below would run in a couple of seconds.

Please Login or Register  to view this content.

[matrex]

XLent that works great, although a loop is used but it sure runs fast. Thanks so much. Will give cerdit for solving the issue

One more thing if you can help with that. How do I sort Arr3 in a reasonably fast way. The standard bubble sort takes ages, and since the number of elements are more than the number rows I cannot utize Excel sort routine

Thanks again

[Firefly2012]

With using so much data you might well be best off doing this in a database (eg like Access) which won't have a 1million or so record limit. If I may ask, why are you using such huge arrays? What do you want to do with the data?

[XLent]

For ref. re: VBA Arrays you can't do much better than Chip Pearson's site: http://www.cpearson.com/Excel/VBAArrays.htm

See also: "sorting an array" and his QSortInPlace

[matrex]

No his sort routines don't work on my data. QSortInPlace is for single dimensional arrays, and worksheet sort cannot take all the data.

Thanks

For ref. re: VBA Arrays you can't do much better than Chip Pearson's site: http://www.cpearson.com/Excel/VBAArrays.htm

See also: "sorting an array" and his QSortInPlace

[mikerickson]

Are you sure you need another array, Perhaps two functions would work for you.
(These functions assume that Array1 and Array2 are 2 dimensional column wise arrays.
Machinery for generalizing could be added, but would obscure the concept)

What are you doing with those arrays? Sorting? Let me see what I can come up with.

Please Login or Register  to view this content.

[mikerickson]

Well, I did a little testing and the pseudo joining that I talked about slows the sorting down by a factor of 2.

You might want to look at the SortLoopJoinedRanges routine in the attached.
It uses this looping to create a third array.

Please Login or Register  to view this content.

Edit: The attachment is corrupted. A new attachment is a few posts below. I can't find "remove attachment" on this new interface.

[matrex]

[ myArray(BreakPoint + i) = BArray(i, 1)


Hi mikerickson

I am not sure what the code does but I am getting subscript out of range on the above line. Also that zip file appears to have got corrupted on uploading. Could you upload it again.

Thanks

[mikerickson]

I don't know why that is erroring. What that line is doing is reading two 2 dimensional arrays, AArray(1 to n, 1 to 1) BArray(1 to m, 1 to 1), into a one dimesional array myArray.

Here is the new upload

[mikerickson]

I just realized that something must be choking on the 2M of random data in the file.
This has less data and should work.

[matrex]

So that is a not a sorting routine, correct?

I am looking for a sorting routine that can sort a 1.5 million element array in a fairly quick way

[mikerickson]

This is a sorting routine for a one D array.
Note the module wide variables, LStack, HStack and sPointer
The sub Main shows how to call it.
One option would be to copy Swap into the three places that it is called. To replace the Call with the explicit code.

Please Login or Register  to view this content.