Does anyone know how to do this please?

• Column.A (A1 to A9) contains the result of function RAND().

• Column.B contains the number 01 to 09.

• Columns(“A:B”) sorted (ascending) 99 times automatically.

• Upon each cycle, take the new sorted values from Column.B and place them next available Column as follows:

• 1st cycle, Column.C = Column.B

• 2nd cycle, Column.D = Columm.B

• 3rd cycle, Column.E = Columm.B

• and so on…

Are you trying to create 99 sets of 1 - 9, with each set sorted randomly? If so, it can be done without a macro.

In A1, enter this formula and drag it down to A9. Ignore the #N/A errors for now.

=INDEX(ROW($A$1:$A$9),RANK(A10,A$10:A$18))

In A10, enter this formula and drag it down to A18:

=RAND()

A1:A9 should now contain a random set of 1 - 9

Now, select A1:A18 and drag it across to Column CU which will give you 99 sets of randomly sorted 1 - 9.

Note that since RAND() is volatile, each time the sheet recalculates you will get a different 99 sets of numbers. If you need to lock in a set, select A1:CU9 and do a Copy...PasteSpecial...Values.

You can actually put the RAND() function cells anywhere you want as long as you reference them correctly in the INDEX formula. In other words, you can "hide them" someplace else in the workbook if you want.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Fantastic, it works brilliantly. You've made my day.

Thanks a million.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History