Generate Unique Random lettes 'a' to 'z' for a row ?

[VishalT]

I am working on a task that requires me to have unique random values from 'a to z'. (it could even be 1 to 26, so, either all numbers or all letters).

In one row + 26 columns, it would have 1 letter each from a to z, without any letter being repeated. (example image attached).

Is it possible to do this for 40,000 + rows? without copying the same sequence? i.e. each row, would have it's own random sequence and hence the chances of any two row being similar would be significantly low.

If anyone can help, it would be very much appreciated.

Thank you.

[mehmetcik]

It is impossible without a Macro. A formula cannot handle the no duplicates issue

You and either Call the Macro Directly or use a user defined function.

I think that there are 10888869450418400000000000000 combinations

So it should be simple to Delete duplicate rows. And have 40000 unique rows.

[VishalT]

I am relatively new to Excel & would not even know how to go about writing a Macro or what it looks like or how short/long it would be.

If it is possible & not too much trouble for you, would you be able to write one? however, if it is something that you can't or that would take a long time, any idea on how I would go about trying to solve this problem?

Thanks for the help & guidance, I appreciate it.

[MrShorty]

I'm a little more optimistic than mehmetcik that this can be done without a macro -- unless, of course, you insist that you cannot use helper columns/cells for the task.

Whatever programming language you choose to do this in, the basic algorithm I would expect to use is often called a "shuffle" -- because the idea is that you want all 26 elements in a random order. In this kind of algorithm, you assign a unique random number (in Excel, you would use the RAND() function) to each of your 26 letters/numbers/elements. Then you sort the list by those random numbers (maybe using the SMALL() or LARGE() functions in Excel). If I were setting something like this up, I would probably:

1) In A1, enter =RAND(). Copy/paste/fill across to Z1.
2) In AA1, enter =MATCH(SMALL($A1:$Z1,COLUMN(A1)),$A1:$Z1,0) where
2a) The SMALL(...) function returns the kth smallest random number where k is determined by the COLUMN() function.
2b) the MATCH(...) function then returns the column number for that particular random number.

That should give you the numbers 1 to 26 in random order. Then copy that sequence down as many rows as needed to get that many random shuffles.

Of course, this approach does not guarantee that you will get 40000 unique sequences. You will then either need to check for duplicate "shuffles" and reject them. Or, if having duplicate sequences is a very real problem/concern, you could build a (painfully long) list of every possible sequence, then "shuffle" that list instead.

Does that sound like an approach you can use?

[mehmetcik]

Please Login or Register  to view this content.

[Rick Rothstein]

Here is a macro that will produce 40,000 guaranteed unique randomized sequences of the letters A thru Z...

Please Login or Register  to view this content.

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ShuffleAlphabet40000Times) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.


EDIT NOTE: I should add for future viewers of this thread that my code will only work on a PC (it will not work on a Mac).

[ChemistB]

An example of what Mr Shorty was explaining. In Column AA, I concatenated and did a conditional formatting to color duplicates red. I still have the Rand() function on Sheet2 so the sequences will be changing every time the workbook does a calculation. To stop that, Copy >Paste Values on Sheet2. I had set it up for 10000 rows but had to shrink it for file size purposes.

[VishalT]

Hello MrShorty

You are very helpful, however perhaps because of lack of Excel understanding on my part, I wasn't able to grasp all of it.. However, while trying to follow search for right directions & follow suggested steps I noticed the macro from Rick, which worked ..

Thank you.

[VishalT]

Hi Mehmetcik,

I tried this, however, it gave me error 1004 & asked me to 'End' it or 'Debug' ..

[VishalT]

Hi Rick,

This macro worked like a charm.. really awesome.., you are a life saver. Thanks

[VishalT]

Hi ChemistB,

I looked at the file & it does seem to make sense, however for now, it seems that Rick's Macro did the trick & considering the deadline .. I am going to get it done using that.. Thank you for taking time and provding example, it does mean a lot.

You all have been wonderful.. after trying several things I was frustrated, however you all made the problem look so easy.. Made my whole weekend much easier ... Thanks again.

[hrlngrv]

This could be done directly with rather inefficient formulas.

A1: =CHAR(RANDBETWEEN(97,122))
B1: =CHAR(SMALL((COUNTIF($A1:A1,CHAR(ROW($97:$122)))=0)*ROW($97:$122),RANDBETWEEN(COLUMNS($A1:B1),26)))

Fill B1 right into C1:Z1.

When you get a version of Excel with spilled formulas and dynamic arrays, this becomes more direct.

A1: =SORTBY(CHAR(SEQUENCE(1,26,97,1)),RANDARRAY(1,26,0,1),1)

which will spill into A1:Z1.

[BMV]

hrlngrv , Good job, It could be not uncial string but probability is very low.
My version was

Please Login or Register  to view this content.