Hi there,

I'm trying to come up with a random name generator - what I've created
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 in
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.


--
Smeeg
------------------------------------------------------------------------
Smeeg's Profile: http://www.excelforum.com/member.php...o&userid=29099
View this thread: http://www.excelforum.com/showthread...hreadid=518314

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

Smeeg wrote:
Hi there,

I'm trying to come up with a random name generator - what I've created
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 in
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, Microsoft MVP - Excel
http://www.andypope.info

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

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.


In article ,
Smeeg wrote:

Hi there,

I'm trying to come up with a random name generator - what I've createdso 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 inB1 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.--
Smeeg------------------------------------------------------------------------S
meeg's Profile:
http://www.excelforum.com/member.php...erid=29099View this
thread: http://www.excelforum.com/showthread...hreadid=518314

Thanks Andy - yours works a treat.

JE McGimpsey - I tried array entering your formula in C1:E1, but the
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,

AJ


--
Smeeg
------------------------------------------------------------------------
Smeeg's Profile: http://www.excelforum.com/member.php...o&userid=29099
View this thread: http://www.excelforum.com/showthread...hreadid=518314

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.


--
random1970
------------------------------------------------------------------------
random1970's Profile: http://www.excelforum.com/member.php...o&userid=32112
View this thread: http://www.excelforum.com/showthread...hreadid=518314

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

In article ,
Smeeg wrote:

Any idea what I'm doing wrong here?