Working with Random Text & Combining Cells

[Louise]

Hello,

I have been reading everything I can and have not come up with the
solution and I am green to excel. Please assist me with this.

I have a list of 137 cells in A1:A137, each cell has data and none of
the cells are duplicates.

I want to randomly combine 10 of the cells together into B1:B137
separating each piece of data by a comma and repeat down the column so
that I have 137 cells with combined data.

Hope that makes sense?

Any help would be fantastic.

Thanks,

Louise

 

[Guest]

Louise,

Here is one formula that should work:

=INDEX($A$1:$A$137,RAND()*10)&","&INDEX($A$1:$A$137,RAND()*10)&","&INDEX($A$1:$A$137,RAND()*10)&","&INDEX($A$1:$A$137,RAND()*10)&","&INDEX($A$1:$A$137,RAND()*10)&","&INDEX($A$1:$A$137,RAND()*10)&","&INDEX($A$1:$A$137,RAND()*10)&","&INDEX($A$1:$A$137,RAND()*10)&","&INDEX($A$1:$A$137,RAND()*10)&","&INDEX($A$1:$A$137,RAND()*10)

Place this in B1 and copy down.

The Rand() function will return a random number and it will change whenever
the sheet recalculates. You can preserve the numbers once they are created by
copying the values in column B and then Paste Special: Values over the top.

Hope that helps.

 

[Guest]

Oops,

Sorry Louise, posted too quick.

In that formula, please change the " *10 " after the Rand() function to "
*137 ".

Please leave out the quotation marks. So the formula should read:

=INDEX($A$1:$A$137,RAND()*137)&","&INDEX($A$1:$A$137,RAND()*137)&","&INDEX($A$1:$A$137,RAND()*137)&","&INDEX($A$1:$A$137,RAND()*137)&","&INDEX($A$1:$A$137,RAND()*137)&","&INDEX($A$1:$A$137,RAND()*137)&","&INDEX($A$1:$A$137,RAND()*137)&","&INDEX($A$1:$A$137,RAND()*137)&","&INDEX($A$1:$A$137,RAND()*137)&","&INDEX($A$1:$A$137,RAND()*137)

Hope THAT works.

 

[mrice]

Louise

I've put together a user defined function for you which I hope does
what you want (please let me know directly if it doesn't).

Please look at the attachment. You'll find the code for the UDF on the
module1 sheet.


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4823 |
+-------------------------------------------------------------------+

 

[Guest]

Louise,

These formulas have the possibility of listing the same cell twice. That is,
it may duplicate a cell entry in column B.

 

[mrice]

In the UDF, I allowed for this and you should only get unique values.

 

[Guest]

I figured. <g>

That's why I wanted to give her a head's up.

 

[dalmuti]

That was exactly what I was looking for. I knew I would never figure it
out. Thanks so much.

Louise

 

[Louise]

Wow, haven't tried it yet...but I appreciate the responses. This will
be a piece that I will use over and over.

I will let you know if I have any issues.

Thanks so much,

Louise

 

[Guest]

For completeness sake, if anyone is following up, here is a formula method
modified from a Peo post back in 2002:

Insert a new column B. Type the Rand() function and copy down.

In C1 type the formula:

=INDEX($A$1:$A$136,MATCH(SMALL($B$1:$B$136,1),$B$1:$B$136,0))&","&INDEX($A$1:$A$136,MATCH(SMALL($B$1:$B$136,2),$B$1:$B$136,0))&","&INDEX($A$1:$A$136,MATCH(SMALL($B$1:$B$136,3),$B$1:$B$136,0))&","&INDEX($A$1:$A$136,MATCH(SMALL($B$1:$B$136,4),$B$1:$B$136,0))&","&INDEX($A$1:$A$136,MATCH(SMALL($B$1:$B$136,5),$B$1:$B$136,0))&","&INDEX($A$1:$A$136,MATCH(SMALL($B$1:$B$136,6),$B$1:$B$136,0))&","&INDEX($A$1:$A$136,MATCH(SMALL($B$1:$B$136,7),$B$1:$B$136,0))&","&INDEX($A$1:$A$136,MATCH(SMALL($B$1:$B$136,8),$B$1:$B$136,0))&","&INDEX($A$1:$A$136,MATCH(SMALL($B$1:$B$136,9),$B$1:$B$136,0))&","&INDEX($A$1:$A$136,MATCH(SMALL($B$1:$B$136,10),$B$1:$B$136,0))

Copy C1, Paste Special:Values in each cell down to C137.

 

[Guest]

Third time's a charm...

Rand()*137 should be Rand()*137+1