Solved EXCEL MACRO: Sorting columns and transposing

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.

The information on Computing.Net is the opinions of its users. Such
opinions may not be accurate and they are to be used at your own risk.
Computing.Net cannot verify the validity of the statements made on this
site. Computing.Net and Compnet Ventures, LLC hereby disclaim all responsibility
and liability for the content of Computing.Net and its accuracy.