Serving the Quantitative Finance Community

• 1
• 2

olayemi
Topic Author
Posts: 17
Joined: June 17th, 2014, 4:27 pm

Shuffling arrays- EXCEL VBA

Hi All,I have a mini-project pls kindly help me out. Am trying to shuffle this array and randomly pick two of these at once:numtop(1) = 1topsuit(1) = "H"numbck(1) = 2bcksuit(1) = "H"numtop(2) = 1topsuit(2) = "H"numbck(2) = 3bcksuit(2) = "H"numtop(3) = 1topsuit(3) = "H"numbck(3) = 4bcksuit(3) = "H"numtop(4) = 1topsuit(4) = "H"numbck(4) = 5bcksuit(4) = "H"numtop(5) = 1topsuit(5) = "H"numbck(5) = 6bcksuit(5) = "H"numtop(6) = 2topsuit(6) = "H"numbck(6) = 3bcksuit(6) = "H"so the output should look like :1H 3H2H 3HAnd also to be able to shuffle the arrays pls.

Orbit
Posts: 507
Joined: October 14th, 2003, 5:34 pm

Shuffling arrays- EXCEL VBA

Well I don't understand the substance of the problem, but it looks like you need to choose numtop(),topsuit(),numbck() and bcksuit(), each of which are indexed from 1 to 6.If the distribution of these choices is "even," (i.e. they occur with equal chance) then simply use the Excel function "=Randbetween(1,6)" for each index choice.HTH

olayemi
Topic Author
Posts: 17
Joined: June 17th, 2014, 4:27 pm

Shuffling arrays- EXCEL VBA

Yes that is what I thought too and did initially but my boss wants each of this array to be shuffled , picked at random and concatenated with the random suits to form a representation of a card such as : 1H 2S 2C 3Hand this should be the output on the spreadsheet.

Orbit
Posts: 507
Joined: October 14th, 2003, 5:34 pm

Shuffling arrays- EXCEL VBA

Ok again not really 100% sure what you mean, but maybe you mean sampling without replacement?Here is an excel user-defined function that will do that:http://www.mcgimpsey.com/excel/udfs/randint.htmlAlso google "sampling without replacement Excel"HTH

olayemi
Topic Author
Posts: 17
Joined: June 17th, 2014, 4:27 pm

Shuffling arrays- EXCEL VBA

Thank you so much. I have a strong feeling this will be very helpful. Cheers

tagoma
Posts: 18775
Joined: February 21st, 2010, 12:58 pm

Shuffling arrays- EXCEL VBA

So your 'boss' asked you that? What kind of companies are you working with?

bearish
Posts: 6627
Joined: February 3rd, 2011, 2:19 pm

Shuffling arrays- EXCEL VBA

QuoteOriginally posted by: tagomaSo your 'boss' asked you that? What kind of companies are you working with?A six card monte shop?

mutley
Posts: 745
Joined: February 9th, 2005, 3:51 pm

Shuffling arrays- EXCEL VBA

rarely do you find a stranger requirement posed here than in the opening post. who shuffles arrays?

Cuchulainn
Posts: 64980
Joined: July 16th, 2004, 7:38 am
Location: Drosophila melanogaster
Contact:

Shuffling arrays- EXCEL VBA

QuoteOriginally posted by: mutleyrarely do you find a stranger requirement posed here than in the opening post. who shuffles arrays?On Wall Street?See also Knuth, Vol. II section 3.4.2. Algorithm S.You might want n unbiased records at random from a file containing N records? Don't know about VBA but in C++11 you could use stuff like shuffle
Last edited by Cuchulainn on April 20th, 2015, 10:00 pm, edited 1 time in total.
"Compatibility means deliberately repeating other people's mistakes."
David Wheeler

http://www.datasimfinancial.com
http://www.datasim.nl

olayemi
Topic Author
Posts: 17
Joined: June 17th, 2014, 4:27 pm

Shuffling arrays- EXCEL VBA

Thank you. ITS SORTED.Thank you.

mutley
Posts: 745
Joined: February 9th, 2005, 3:51 pm

Shuffling arrays- EXCEL VBA

Cuch, I have never once shuffled an array. I will inform you by carrier pigeon when I finally do

Cuchulainn
Posts: 64980
Joined: July 16th, 2004, 7:38 am
Location: Drosophila melanogaster
Contact:

Shuffling arrays- EXCEL VBA

QuoteOriginally posted by: mutleyCuch, I have never once shuffled an array. I will inform you by carrier pigeon when I finally do :)Mutley,There's only one thing worse than shuffling arrays and that's _not_ shuffling arrays.
Last edited by Cuchulainn on April 23rd, 2015, 10:00 pm, edited 1 time in total.
"Compatibility means deliberately repeating other people's mistakes."
David Wheeler

http://www.datasimfinancial.com
http://www.datasim.nl

Polter
Posts: 2526
Joined: April 29th, 2008, 4:55 pm

Shuffling arrays- EXCEL VBA

You can even implement your own sorting algorithm in Excel:http://www.slideshare.net/Felienne/spre ... ru5d4GPk// Seriously, though, if you work with Excel spreadsheets on a daily basis some of the described practices should be mandatory -- e.g., naming things...
Last edited by Polter on April 24th, 2015, 10:00 pm, edited 1 time in total.

Cuchulainn
Posts: 64980
Joined: July 16th, 2004, 7:38 am
Location: Drosophila melanogaster
Contact:

Shuffling arrays- EXCEL VBA

She's speaking American English. It's a bit exaggerated.
Last edited by Cuchulainn on April 24th, 2015, 10:00 pm, edited 1 time in total.
"Compatibility means deliberately repeating other people's mistakes."
David Wheeler

http://www.datasimfinancial.com
http://www.datasim.nl

olayemi
Topic Author
Posts: 17
Joined: June 17th, 2014, 4:27 pm

Thanks