need fast row shufle

[Master_Splinter]

I have been using this great website for a while, but I finally came across something that has stumped me, and I haven't seen in other posts.
So here is my first post.

So I have code that 'works', but I feel like it could be better. (And to be usable, it has to be)

My problem involves a large array (3000 columns by 5000 rows)
What I'm looking for is an efficient way to randomize the items in each row.
so array1 = [1,2,3,4;5,6,7,8] could become array1 = [3,2,4,1; 7,6,5,8] (or something similar)

My solution now is to shuffle the array in place.
I iterate though the array, pick a random member of the row and swap them.

'code
'buys is defined earlier as buys(1 to 5000,1 to 3000) as integer
dim i as integer
dim j as integer
dim k as integer
dim rndm as integer
for i = 1 to 5000
For j = 1 To 3000
k = buys(i, j)
rndm = Floor(Rnd * 3000, 1) + 1
buys(i, j) = buys(i, rndm)
buys(i, rndm) = k
Next j
next i
'end code

So this works, but it seems to take longer than I would suspect.
My guess is there is a performance hit from all the buys(i,j) references and such, but I'm not sure.
I tried setting rdmn to a const like 1, and that didn't affect performance, so I don't think its the rnd function.

Any ideas on how to speed this up?

Thank you,

-Dave

 

[ChrisM]

There's nothing wrong with your "buys" references, they won't slow you down. You really can't write that routine much shorter than you've already done.

I'd only change two things:

1. Change your Integer variables to Long

2. Don't use the Floor function (it's an older deprecated worksheet.function). Just use INT() to strip out decimal places instead.

 

[wigi]

Hi Dave

I don't think you can do much better. After all, it's only a handful of seconds on a decent computer.
We're talking about 15 million loops...

 

[Master_Splinter]

Thank you both.
I did change the floor to int, and all my integers to long, and it seems to be a little better.
My big improvement came from shuffling the original array as I was making it, so I could avoid another loop through.
Yes its pretty fast, it just I'm hoping to run this thousands of times; I'm using it kind of like a monte-carlo simulator, but with real stock values.

Hooray!

Thanks again.

-Dave

 

[wigi]

You're welcome.

For such analyses, are you sure that MS Excel is the right tool for you?

By the way, thank you for using

 tags in future posts. They will format/colourize the code on the board here.

 

[Master_Splinter]

You are probably right.
I probably should be using R or python.
I've just been able to do so much with excel up to this point that it has been hard to motivate myself to switch.

So Close!

-Dave