?
Solved

Random generations

Posted on 2012-08-23
11
Medium Priority
?
551 Views
Last Modified: 2012-08-24
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
Comment
Question by:mikecox_
  • 6
  • 3
10 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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

by:redmondb
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

by:mikecox_
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:mikecox_
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

by:
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

by:mikecox_
ID: 38328242
Awesome; exactly what I was looking for! Thanks
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 38329572
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
0
 

Author Comment

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

Author Closing Comment

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

Thanks
0
 

Author Comment

by:mikecox_
ID: 38330817
Did it work this time?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question