Excel Help

[smegballs]

I'm having trouble with making a letter generator in Excel.

https://i46.tinypic.com/652vex.jpg

That is the format of how I want it to read. When F9 is pressed (recalc) the letters will be randomly generated from A-Z within the bottom row. I have got this far using random number generator, conditioning the random number and using a VLOOKUP table to return a letter value.

However in my application there can only be one of each letter per row otherwise it won't work. With a simple random number there is nothing to stop the same letter being generated twice within the row. Is there an "easy" way to make a unique letter for each position or is this heading into proper algorithm territory?



if you know what it is!

Some people WILL have seen this or something very similar before

[FreshAL]

3 columns: A B C D E
in A place 1 to in decreasing numerical order
in B place letters a-z, in alphabetical order starting with 'a' in cell B1
in C1 place "=rand()" can copy this down to C26
in D1 place "=RANK(C1,$C$1:$C$26)" and copy down to D26
in E1 place "=VLOOKUP(D1,$A$1:$B$26,2)" and copy down to E26


F9 now gives your index number in column A and your randomly sorted letter in column E

[smegballs]

Nice one, I'm having a play now so will give this a go!

EDIT: Works a treat! What also good is I can see how its working! Haven't used excel in a while so I'm rusty.


I don't suppose you know quite how "random" the random number function is? I've heard that apparently some are not that random due to the way a computer works.

[Charlie]

How random do you want it? A computer program will never be truly random, unless it looks thermal noise from some electronic component.

I may have remembered the bit thermal noise wrong, it was mentioned in a lecture in passing and I was tired
____________________
Past: Honda x8rs, Honda City fly, Honda Hornet 250, Honda VFR750, Yamaha xt600e.
Current: Honda CBR929RR & Yamaha XT660Z Tenere

[smegballs]

I just had a google and it seems most "random" numbers are made by psuedorandom generators.

I'm working on an encryption matrix *Big hint for the guessy game* so the more random the better, but I doubt it will be attacked with mega hardware so computer random should be fine for the moment.

[Alexio]

[Charlie]

Not sure how Excel generates it, however a lot of programs use unix time as the starting point for the random number. So if you know the exact time a random number is created and what was used to create it, it could be possible to figure it out.

Like I say I am definitely sure how much of the stuff I am telling you is correct!

Edit:
I was still posting while the last reply was submitted. Never mind.
____________________
Past: Honda x8rs, Honda City fly, Honda Hornet 250, Honda VFR750, Yamaha xt600e.
Current: Honda CBR929RR & Yamaha XT660Z Tenere

[smegballs]

Well its all working nicely now, a big matrix with no repetitions or other unwanteds.

Thanks again AL.