• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How To Do This in Excell?

A

akuuka

New Member
I need help to create an excell formulas to produce a random number (consist of 4 number, 3 number, and 2 number) from 5 number given. Please help me.


Thank's
 
It's not quite clear what you are trying to accomplish with the 4, 3, and 2 number. You can either use this formula:

=RANDBETWEEN(1,5)

to generate a random number between 1 and 5, or, if you already have a list of numbers that you want to pick from, put this formula in the cells next to each number:

=RAND()


And you can then use a formula like this to get a random number from your list:

=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,0))
 
Hi Akuuka,


My understanding of your requirement is that you have some numbers in a cell eg B3 = 12345, and you want to generate random 4 digit number consisting of numbers from B3, like wise 3 digits and 2 digits number. am I right ?


If yes, you can try some thing like this:

=MID(B3,RANDBETWEEN(1,5),1)&MID(B3,RANDBETWEEN(1,5),1)


the above formula will generate random two digit number consisting of numbers from B3.


Keep adding &MID(B3,RANDBETWEEN(1,5),1) to get additional digit.


Regards,

Prasad DN
 
Akuuka,

If the formulas stated so far aren't what you were looking for, could you provide a quick example of what you have (if anything) and what you would like to see?
 
Thank's for fast reply and sorry for my english language, couse it's my 2nd language (i'm indonesian)


@Luke M : My requirement is like what prasaddn explain.

@prasaddn : thanks' a lot. That what i'm looking for. But how if we add a new rules that there's no repeat number in 4, 3 or 2 digit numbers that's generate from B3?
 
Here's an example:

input:01234

will produce :

120 number of 4 digit

0123 : 0124 : 0132 : 0134 : 0142 : 0143 : 0213 : 0214 : 0231 : 0234 : 0241 : 0243 : 0312 : 0314 : 0321 : 0324 : 0341 : 0342 : 0412 : 0413 : 0421 : 0423 : 0431 : 0432 : 1023 : 1024 : 1032 : 1034 : 1042 : 1043 : 1203 : 1204 : 1230 : 1234 : 1240 : 1243 : 1302 : 1304 : 1320 : 1324 : 1340 : 1342 : 1402 : 1403 : 1420 : 1423 : 1430 : 1432 : 2013 : 2014 : 2031 : 2034 : 2041 : 2043 : 2103 : 2104 : 2130 : 2134 : 2140 : 2143 : 2301 : 2304 : 2310 : 2314 : 2340 : 2341 : 2401 : 2403 : 2410 : 2413 : 2430 : 2431 : 3012 : 3014 : 3021 : 3024 : 3041 : 3042 : 3102 : 3104 : 3120 : 3124 : 3140 : 3142 : 3201 : 3204 : 3210 : 3214 : 3240 : 3241 : 3401 : 3402 : 3410 : 3412 : 3420 : 3421 : 4012 : 4013 : 4021 : 4023 : 4031 : 4032 : 4102 : 4103 : 4120 : 4123 : 4130 : 4132 : 4201 : 4203 : 4210 : 4213 : 4230 : 4231 : 4301 : 4302 : 4310 : 4312 : 4320 : 4321 :


60 number of 3 digit

012 : 013 : 014 : 021 : 023 : 024 : 031 : 032 : 034 : 041 : 042 : 043 : 102 : 103 : 104 : 120 : 123 : 124 : 130 : 132 : 134 : 140 : 142 : 143 : 201 : 203 : 204 : 210 : 213 : 214 : 230 : 231 : 234 : 240 : 241 : 243 : 301 : 302 : 304 : 310 : 312 : 314 : 320 : 321 : 324 : 340 : 341 : 342 : 401 : 402 : 403 : 410 : 412 : 413 : 420 : 421 : 423 : 430 : 431 : 432 :


20 number of 2 digit

01 : 02 : 03 : 04 : 10 : 12 : 13 : 14 : 20 : 21 : 23 : 24 : 30 : 31 : 32 : 34 : 40 : 41 : 42 : 43 :


so how we do this in Excell?
 
Ah, the old "list combinations" problem. You'll need to use VBA to do this. This thread looks very similar:

http://www.excelforum.com/excel-programming/401473-generate-table-of-combinations.html
 
You must log in or register to reply here.
Back
Top