Random Sorting Columns 2

[sanders720]

Lets say I have a column with Names entered, and I would like to jumble them up, in no particular (in other words random) order. How could I accomplish this task?

 

[dougcranston]

sanders720

Assuming you are using Excel.

One solution would be to add a column.

In that column type in the following formula:

=RAND()

Then copy it to each of the cells in that column equal to the # of rows in your spreadsheet.

Each time you open the sheet it will generate new random numbers.

Then you just sort on that column instead of the names to get a random order.

If you are talking about data in Access you can assign a field with AUTONUMBER and then set it to RANDOM.

Word, no inherent autonumber so you might have to create pseudo random, by snagging date/time to the millisecond and then sort on it, or convert it to a sequential # and then do a RIGHT() to pull only certain # of digits. Stretching but a thought.

Hope this helps.

DougCranston

 

[TonyJollans]

Hi sanders720,

Could you have another column of random numbers (every cell containing =RAND()), and sort on it? The end result of your sort would not be in the order of that column because the random numbers get recalculated all the time (unless you have calculation disabled) but that shouldn't matter as they're just a means to an end.

Enjoy,
Tony

 

[TonyJollans]

Hi sanders720,

Could you have another column of random numbers (every cell containing =RAND()), and sort on it? The end result of your sort would not be in the order of that column because the random numbers get recalculated all the time (unless you have calculation disabled) but that shouldn't matter as they're just a means to an end.

Enjoy,
Tony

 

[sanders720]

Yes, I'm using Excel. Forgive my ignorance, but I don't understand any ot this. does rand() define the whole column, or a range of a column? I do not want to duplicate anything either.

Let's say I have this column on the left. I would like to change it (or copy it to another is fine) to the column on the right. My application also uses string values, not numbers.

1 7
2 9
3 2
4 4
5 1
6 5
7 3
8 6
9 8


Thanks for your help thus far!

 

[dougcranston]

sanders720,

The use of =RAND() generates a random number.

It only applies to the cell or formula you are creating.

Based on your original request, I/we interpreted your need to "randomize" entries in one field. Excel provides a SORT function, but that will only sort ASCENDING or DESCENDING not randomly.

One technique is the one I and TonyJollans offered up.

You would leave your existing data intact. You would just create a new column, and in that column drop in the =RAND() into each cell, next to your data. That completed you would define the range to include al of your data AND this additional column with the random #'s and SORT ON THAT COLUMN with the RANDOM #'s either in ascending or descending order. Do this randomizes your other data.

Now your new requirement for dealing with text. Short of writing a VBA function and then using it to assign a unique number/letter/character is out of my league, and one must assume yours if your not clear on RAND() function.

Hope this clears things up and helps.
DougCranston

 

[sanders720]

Lets say column A has row numbers in numerical order, column B has the textual data I am looking for anc column C has '=rand(A:A)' to select a random number based on the contents of column column A. Is this the correct approach, and for some reason I get an error in the formula.

Thanks again!!!

 

[dougcranston]

sanders720,

Ok. Based on your notes in your last msg.

Col1=Numerical Data in Ascending order
Col2=Textual data
Col3=Rand function

the Rand function will generate a random # between 0 & 1

sample
A B C
1 John Doe =Rand()
2 Billy Bob =Rand()
3 Charles Manns =Rand()
.
.
so on.

Then highlight the A1 to C3 (in this example).

Click on the DATA / SORT / SORT BY select Column C (in this case) and click OK.

It will REORDER your information based on the info in each ROWS associated COLUMN C the random #.

The value for Col A and Col B will stay together but be reordered based on what random # is in Col C

Hope this helps.

If not, reply to this msg with your email address and I will dummy up an Excel spreadsheet for you.

DougCranston

 

[sanders720]

Thanks, I figured it out!

 

[dougcranston]

Glad you got it to work and thanks for the star.

DougCranston

 

[DaleWatson123321]

sanders,

As an "added touch", I've developed a file you might find really useful.

It's set up to allow for the following - at a click of a button.

1) Generates random numbers for whatever list of names are entered - thereby randomly mixing the names.

It allows for assigning a separate series of numbers - such as "membership" numbers or whatever you decide.

2) You can then sort (by clicking one of 3 sort buttons) - for: a) sequential number, b) name, or c) the separate series of numbers.

If you'd like the file, email me and I'll send it by return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca