Working with Random Text & Combining Cells
 

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

Working with Random Text & Combining Cells
 

Louise,

Here is one formula that should work:

=INDEX($A$1:$A$137,RAND()*10)&","&INDEX($A$1:$A$13 7,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)&","&I NDEX($A$1:$A$137,RAND()*10)&","&INDEX($A$1:$A$137, RAND()*10)&","&INDEX($A$1:$A$137,RAND()*10)&","&IN DEX($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.

"Louise" wrote:

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

Working with Random Text & Combining Cells
 

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$1 37,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()*13 7)&","&INDEX($A$1:$A$137,RAND()*137)

Hope THAT works.



"Louise" wrote:

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

Working with Random Text & Combining Cells
 

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 |
+-------------------------------------------------------------------+

--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=546799

Working with Random Text & Combining Cells
 

Louise,

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



"Louise" wrote:

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

Working with Random Text & Combining Cells
 

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


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=546799

Working with Random Text & Combining Cells
 

I figured. <g

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


"mrice" wrote:


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


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=546799

Working with Random Text & Combining Cells
 

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

Louise


--
dalmuti
------------------------------------------------------------------------
dalmuti's Profile: http://www.excelforum.com/member.php...o&userid=34956
View this thread: http://www.excelforum.com/showthread...hreadid=546799

Working with Random Text & Combining Cells
 

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

Working with Random Text & Combining Cells
 

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,M ATCH(SMALL($B$1:$B$136,3),$B$1:$B$136,0))&","&INDE X($A$1:$A$136,MATCH(SMALL($B$1:$B$136,4),$B$1:$B$1 36,0))&","&INDEX($A$1:$A$136,MATCH(SMALL($B$1:$B$1 36,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.


"Louise" wrote:

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

Working with Random Text & Combining Cells
 

Third time's a charm...

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

"Dominic LeVasseur" wrote:

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$1 37,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()*13 7)&","&INDEX($A$1:$A$137,RAND()*137)

Hope THAT works.



"Louise" wrote:

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