need fast row shufle

Master_Splinter

New Member
Joined
Feb 14, 2013
Messages
3
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,950
Office Version
365
Platform
Windows, Mobile, Web
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

New Member
Joined
Feb 14, 2013
Messages
3
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,950
Office Version
365
Platform
Windows, Mobile, Web
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
Code:
 tags in future posts. They will format/colourize the code on the board here.
 

Master_Splinter

New Member
Joined
Feb 14, 2013
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,967
Messages
5,471,747
Members
406,780
Latest member
Todd Gentry

This Week's Hot Topics

Top