I am fairly new to Excel and am using 2007. I have a block of words with all
words being in separate cells which are A6 to I16. I need to perform a random
sort to rearrange these cells (words) so that no two words are located in the
same cells after each sort. I have tried using the RAND() function, but that
will only sort rows, so that each row contains the same words.

I need to randomly sort by column then by row. I would think that this would
be a fairly simple task and that others have asked how to do this exact same
thing, but I am not finding any answers.

I would like to do this without using macros as this is for a 1st grade
class and we are blocked from running macros. If you need more clarification,
I would be glad to provide it.

Thanks to all. Your help is greatly appriciated.

you can insert a column in front of the data and insert the =RAND() function
and copy down. This generates randome numbers. Sort by that column. Then you
can do the same thing with a row above the data and sort by that row.

"Mike T" wrote:

I am fairly new to Excel and am using 2007. I have a block of words with all
words being in separate cells which are A6 to I16. I need to perform a random
sort to rearrange these cells (words) so that no two words are located in the
same cells after each sort. I have tried using the RAND() function, but that
will only sort rows, so that each row contains the same words.

I need to randomly sort by column then by row. I would think that this would
be a fairly simple task and that others have asked how to do this exact same
thing, but I am not finding any answers.

I would like to do this without using macros as this is for a 1st grade
class and we are blocked from running macros. If you need more clarification,
I would be glad to provide it.

Thanks to all. Your help is greatly appriciated.

cm,

Is ther a way to sort both a row and a column at the same time or do I have
to do separate sorts?

Thanks,

Mike

"cm" wrote:

you can insert a column in front of the data and insert the =RAND() function
and copy down. This generates randome numbers. Sort by that column. Then you
can do the same thing with a row above the data and sort by that row.

"Mike T" wrote:

I am fairly new to Excel and am using 2007. I have a block of words with all
words being in separate cells which are A6 to I16. I need to perform a random
sort to rearrange these cells (words) so that no two words are located in the
same cells after each sort. I have tried using the RAND() function, but that
will only sort rows, so that each row contains the same words.

I need to randomly sort by column then by row. I would think that this would
be a fairly simple task and that others have asked how to do this exact same
thing, but I am not finding any answers.

I would like to do this without using macros as this is for a 1st grade
class and we are blocked from running macros. If you need more clarification,
I would be glad to provide it.

Thanks to all. Your help is greatly appriciated.

It took me a little while to figure this out, but it works perfectly. I even
created a button and asinged the button to run the macro after selecting the
cells to use. I don't knwo how to thank you.

"p45cal" wrote:


Mike T;506807 Wrote:
I am fairly new to Excel and am using 2007. I have a block of words with
all
words being in separate cells which are A6 to I16. I need to perform a
random
sort to rearrange these cells (words) so that no two words are located
in the
same cells after each sort. I have tried using the RAND() function, but
that
will only sort rows, so that each row contains the same words.

I need to randomly sort by column then by row. I would think that this
would
be a fairly simple task and that others have asked how to do this exact
same
thing, but I am not finding any answers.

I would like to do this without using macros as this is for a 1st
grade
class and we are blocked from running macros. If you need more
clarification,
I would be glad to provide it.

Thanks to all. Your help is greatly appriciated.

A little macro, this one works on the current selection, so I leave you
to adjust for whichever range you want by changing the line:

Set RangeToRandomnise = Selection

It ensures that no word stays in the same cell before and after the
randomisation (which is what I'm *-guessing -*you want, since 'randomly
sorting' is a bit of an oxymoron):
Sub mixup()
Dim words()
Dim RangeToRandomnise As Range
Set RangeToRandomnise = Selection
ReDim words(RangeToRandomnise.Cells.Count)
i = 1
For Each cll In RangeToRandomnise.Cells
words(i) = cll.Value
i = i + 1
Next cll
For Each cll In RangeToRandomnise.Cells
Do
x = Application.WorksheetFunction.RandBetween(1, UBound(words))
Loop Until cll.Value = "" Or cll.Value < words(x)
cll.Value = words(x)
For i = x To UBound(words) - 1
words(i) = words(i + 1)
Next i
ReDim Preserve words(UBound(words) - 1)
Next cll
End Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=139346