Random Name Generator

[Smeeg]

Hi there,

I'm trying to come up with a random name generator - what I've create
so far is rather cumbersome and there must be a more efficient way.

If column A1:A10 has 10 random words in it, what formula can I use i
B1 which results in something like 'randomword' 'randomword
'randomword' Ltd

e.g A1:A10

elephant
dog
snake
cat
rabbit
worm
butterfly
chicken
tiger
ape

with a random formula in B1, I would like a result which looks like

elephant cat worm Ltd or
chicken tiger chicken Ltd or
rabbit ape dog Ltd etc....

Thanks in advance

 

[Andy Pope]

Hi,

On way,

=INDEX($A$1:$A$10,INT(RAND()*10)+1,1) & " " &
INDEX($A$1:$A$10,INT(RAND()*10)+1,1) & " " &
INDEX($A$1:$A$10,INT(RAND()*10)+1,1) & " Ltd"

Cheers
Andy

 

[JE McGimpsey]

Assuming you want no repetition within a name, you could use the User
Defined Function found here:

http://mcgimpsey.com/excel/randint.html

Select C1:E1 and array enter (CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX($A$1:$A$1000,randint(1,COUNTA($A$1:$A$1000)))


in B1 enter

=C1 & " " & D1 & " " & E1 & " Ltd"

You can then copy B1:E1 down as far as desired. You may have duplicate
strings in column B. See

http://cpearson.com/excel/duplicat.htm

for ways to identify them.

 

[Smeeg]

Thanks Andy - yours works a treat.

JE McGimpsey - I tried array entering your formula in C1:E1, but th
result returns #NAME?

-----------
I highlighted C1:E1

Then I copied and pasted your formula:
=INDEX($A$1:$A$1000,randint(1,COUNTA($A$1:$A$1000)))

Then, I pressed CTRL-SHIFT-ENTER (rather than just ENTER)

Result = #NAME? in cells C1:E1
-----------


Any idea what I'm doing wrong here?

Thanks,

A

 

[random1970]

Insert two columns before the list you want to generate a random result
from. e.g. if your list is in A1:A10, insert two columns so your list
is in C1:C10. In cell B1, enter the formula "=rand()". Fill down to
B10. In cell A1, enter the function "=rank(B1,$B$1:$B$10,0)", and fill
down to A10. This will give each cell from A1 to A10 a unique number
(rank) from 1 to 10.

What this does is build in a random nature to any VLOOKUP reference for
the array which includes your list (in the example above, this array
would be $A$1:$C$10). Every time F9 is pressed, the ranking will
change. So, to get a random list, in any cell you like, type the
following:

=concatenate(vlookup(1,$A$1:$C$10,3,false),"
",vlookup(2,$A$1:$C$10,3,false)," ",vlookup(3,$A$1:$C$10,3,false),"
Ltd")

To make this far easier (I don't like typing in absolute cell
references), you could name your array. Note: The " " parts in the
formula are just there to put spaces in.

Hope this helps. In my experience, there's almost nothing that can't
be done in Excel. Persevere, and think outside the square. You'll
find a solution.

Regards,

random1970

Excel user (and enthusiast) for more years than I care to remember.

 

[JE McGimpsey]

Did you put the UDF I referenced in a regular code module?