Solved

# Random generations

Posted on 2012-08-23
Medium Priority
551 Views
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
0
Question by:mikecox_
• 6
• 3

LVL 81

Expert Comment

ID: 38327601
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
0

LVL 26

Expert Comment

ID: 38327650
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
0

Author Comment

ID: 38327970
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.
0

Author Comment

ID: 38327971
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.
0

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 38328011
You are building a cypher or encoder. See the attached. To change the assignments, sort the list on Sheet2 using column A as the key. Each time you sort the order will randomize.

Kevin
Code.xlsx
0

Author Comment

ID: 38328242
Awesome; exactly what I was looking for! Thanks
0

LVL 81

Expert Comment

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

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
0

Author Comment

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

Author Closing Comment

ID: 38330814
Perfect, exactly what I was looking for!

Thanks
0

Author Comment

ID: 38330817
Did it work this time?
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoā¦
###### Suggested Courses
Course of the Month16 days, 4 hours left to enroll