Generating Random Numbers between Upper & Lower Limits and without Repeats.

Generating Random Numbers between Upper & Lower Limits and without Repeats.

Dear Forum,

I need to randomize few entries ROW-WISE and some entries COLUMN-WISE..
All these entries , i.e names are given a number however I want to randomize the order and I am trying with the rand() function however I keep on getting repeats , I need the entries only once but randomly arranged..

Will need them to have some COlumn-WISE too, the file will be shared with lower versions of Excel 2003 so I cant rrosk of using randbetween() and also the entries are more than 200+.

SO can someone please help me with a good formula..

Warm Regards
e4excel

Hi e4excel,

See http://www.techonthenet.com/excel/formulas/rand.php for building a randbetween using older versions of excel.

It is a little contradictory to say you want random numbers but they can't repeat. I'm not sure you really mean that. If you do then sorting a row of numbers using a helping column has worked for me.

Hi e4excel,

Sorry about not looking back far enough for your reply. The normal way to sort without repeats is to simply sort the rows based on a rand() function in a new column. If you need to do it again you could copy the table and paste it using transpose and do it again. See the attahced that started as a normal multiplication table. I added a column L and sorted by it. Then copy and paste using transpose and did it again. The result is randomly sorted rows and columns.

Hi e4,

I just don't get it. You want to randomize the list on the left to get the center list and then sort the center list to get the list on the left again? As soon as you sort the list on the right to have City and code it will be just like the left table. Won't it?

Dear marvinp,

No that was for your example.
In the actual example I would have a Random List..in the beginnig itself..

But as I want to randomize too for some other file which is almost similar I combined both the things in the list...

Please look it as a Requirement in this format but I need the RANDOMIZING feature with the specifications of an Lower & Upper Limits without any Repeats and then If I can also SORT..

Warm Regards
e4excel

E4, try this

Insert two rows above, then use a helper column eg: column E in the first list & use,

=IF(C5="","",RAND())

copy down.

In I1 enter =COUNT(E:E) to get total used towns.

In the 2nd list use,

=IF(ROWS(G$5:G5)<=I$1,INDEX(A:A,MATCH(SMALL(E:E,ROWS(G$5:G5)),E:E,0)),"")

copy down.

As Marvin Said, If the first list is already sorted, 3rd list will be exactly same as first list.

See the attached. Hope this helps

Hi e4excel,

Have you been able to apply your above CSE fomula to 200+ rows and then resorted them by the City? I'd love to see your final answer using the formula you have supplied from the above. I'm not sure the CSE formula you suggest from DO or DLL would fit in a cell. I also suspect you would need to know the exact number of rows or columns you were dealing with and your original question of "200+" rows implies the number might vary, making the formula not appropriate. If the answers supplied answer your question you can mark this one solved. I believe you have asked a question with an impossible answer. This reminds me of those questions that want a set of random numbers that must add up to a set sum. If the sum is defined then the numbers can't be completely random. I bellieve the question you asked doesn't have an answer, based on your stated criteria. Prove me wrong and use the CSE formula you found to solve your problem.