asked on

Random generations

Is there a way to fill in the empty cells with the alphanumeric characters in yellow, so that they are all added randomly.

In other words, some kind of formula that would shuffle the characters in this spreadsheet and the place them in the empty cells; and to it over and over again, on demand?
Code.xlsx

zorvek (Kevin Jones)

First, create a named constant, "RndCharSet", that contains the set of characters to use in the random strings. The example below creates a set of characters including upper case alpha characters and the numbers 0 through 9.

="ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"

Second, create a named formula, "RndChar", with the formula below.

=MID(RndChrSet,RAND()*LEN(RndChrSet)+1,1)

Use the formula below to create a string of five random characters from the character set.

=RndChr&RndChr&RndChr&RndChr&RndChr

To change the set of characters used to create the random string, edit the characters defined in the named constant "RndCharSet".

Kevin

redmondb

Hi, mikecox.

Please see attached. The formula in B8 is...
=OFFSET($A$2,INT(B9/7),2*(B9-7*INT(B9/7))+1)
... which uses the following formula in B9...
=RANDBETWEEN(1,36)

Hitting F9 (or causing Excel to do a Calculation) will shuffle the characters.

The formulas in B9:O9 can be hidden or simply moved to another sheet.

Regards,
Brian.Code-V2.xlsx

mikecox_

ASKER

Brian,

Thanks for putting in the formula but it doesn't do what I'm asking.

I want the characters to appear beside the one in the yellow columbs.
B2C2, D2E2, F2G2, etc.

The idea is to create random replacement character for the actual characters.

If I want to remember the password for a website called "boot store" I could find the letters in the yellow column but replace them with characters in green column to spell "boot store". But the characters have to be side by side.

mikecox_

ASKER

Kevin, maybe your formula does that, but I have to work out how to set it up. I pretty good with the basics, but when it gets complicated I struggle. If I gets too much for me I'll post another question.

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mikecox_

ASKER

Awesome; exactly what I was looking for! Thanks

zorvek (Kevin Jones)

Any particular reason you chose to close with a "C" grade? Did I miss something?

Here are the grading guidelines:

A: The Expert(s) either provided you with a thorough and informative answer or with a link to information that answered your question.

B: The Expert(s) provided an acceptable solution, or a link to an acceptable solution but you needed more information to complete the task.

C: You have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information, and the answer, after clarification, lacks finality or does not address the issue presented.

Kevin

mikecox_

ASKER

I'm so sorry! I thought I clicked the "A". How do I change it?

mikecox_

ASKER

Perfect, exactly what I was looking for!

Thanks

mikecox_

ASKER

Did it work this time?