number string in random way

[arif]

how to arrange the given data in random way if we give only one command every time it should be change

the sheet is attached

it contains no. 1 to 55

when i select any key the newly arranged data should be displayed that is in random way not in series

please all

 

[bosco_yip]

1] Helper1 H1, copy down to H55 :

=RANK(I1,I$1:I$55)

2] Helper2 I1, copy down to I55 :

=RAND()

3] In A2, formula copy across to F2 and all copy down :

=IFERROR(INDEX($H$1:$H$55,COLUMN(A1)*10+ROW(A1)-10),"")

4] Press F9 for renew random numbers.

Regards
Bosco

 

[PaulM4135]

Hi, I'm not sure what it is you are trying to do here. There is a "RANDBETWEEN" formula if that's what you are looking for...

=RANDBETWEEN(1,55)

That will randomly generate a value between 1 and 55 with a new value being generated each time you recalculate (press F9).

 

[Khalid NGO]

Hi arif,

If you need unique random numbers (from 1 to 55) vertically, you can use this in A2 and copy down:

=LARGE(ROW($1:$55)*NOT(COUNTIF(A$1:A1, ROW($1:$55))), RANDBETWEEN(1,56-ROW(A1)))

See the detail here:
http://www.get-digital-help.com/2009/07/03/how-to-create-a-random-list-of-unique-numbers-in-excel/

Regards,

 

[Hui]

Press F9 for a good time or a new set of random numbers
see attached file:

 

[bosco_yip]

If you need unique random numbers (from 1 to 55) vertically, you can use this in A2 and copy down:
=LARGE(ROW($1:$55)*NOT(COUNTIF(A$1:A1, ROW($1:$55))), RANDBETWEEN(1,56-ROW(A1)))
Regards,

Hi Khalid,

1] A bit shorter, in using "1-" instead of "NOT" :

=LARGE(ROW($1:$55)*(1-COUNTIF(A$1:A1,ROW($1:$55))),RANDBETWEEN(1,56-ROW(A1)))

2] Another option, in using "INT(RAND().." instead of "RANDBETWEEN" :

=LARGE(ROW($1:$55)*(1-COUNTIF(A$1:A1,ROW($1:$55))),INT(RAND()*(56-ROW(A1)))+1)

Regards
Bosco

 

[Khalid NGO]

Hi Bosco,
Incredible...

You are formula Master Thanks for sharing.

Regards,