Oligomer randomizer (try saying that ten times fast)

Posted by Eric on August 29, 2001 1:31 PM

Col(A) has a list of text, one 20 character string per cell. In col(B) I would like to put the same 20 characters as in col(A), but in a random order.

For instance A2 to B2 goes:
CGATTCGGTAACCGGAATGC --> AGTTCAGAGGAACCCTTGGC

What we're doing now is splitting text to columns with a fixed width, transpose pasting it into a column, generating a list of random numbers along side it, sorting by the random numbers, and concatenating the results back into a single cell-

oh my carpal tunnely wrists! help appreciated!

Posted by Mark w. on August 29, 2001 2:19 PM

Carpel Tunnel! Transpose pasting! : ) Just...

...create a template (as described below) that
allows you to enter your "Gattaca" stuff in
column A, and it does the rest!

First, enter the formula, =MID($A$1,COLUMN()-1,1),
into cells B1:U1. Next, enter =RAND() into cells
V1:AO1. Next, enter the formula, =RANK(V1,$V$1:$AO$1),
into cell AP1 and Fill Right to include cell BI1.
Finally, enter the following formula into cell
BJ1 and you're ready to mutate (or whatever):

=MID(A1,OFFSET(AO1,,1),1)
&MID(A1,OFFSET(AO1,,2),1)
&MID(A1,OFFSET(AO1,,3),1)
&MID(A1,OFFSET(AO1,,4),1)
&MID(A1,OFFSET(AO1,,5),1)
&MID(A1,OFFSET(AO1,,6),1)
&MID(A1,OFFSET(AO1,,7),1)
&MID(A1,OFFSET(AO1,,8),1)
&MID(A1,OFFSET(AO1,,9),1)
&MID(A1,OFFSET(AO1,,10),1)
&MID(A1,OFFSET(AO1,,11),1)
&MID(A1,OFFSET(AO1,,12),1)
&MID(A1,OFFSET(AO1,,13),1)
&MID(A1,OFFSET(AO1,,14),1)
&MID(A1,OFFSET(AO1,,15),1)
&MID(A1,OFFSET(AO1,,16),1)
&MID(A1,OFFSET(AO1,,17),1)
&MID(A1,OFFSET(AO1,,18),1)
&MID(A1,OFFSET(AO1,,19),1)
&MID(A1,OFFSET(AO1,,20),1)

Posted by Mark W. on August 29, 2001 2:29 PM

Oh, yeah...

I guess you wanted to view the randomized results
and the original value side-by-side. Either
hide columns B:BI or Cut column BJ and Insert it
as the new column B, then change the formula in
cells C1:V1 to =MID($A$1,COLUMN()-2,1).

Posted by Eric on August 29, 2001 4:29 PM

Thanks Mark! I hope we don't go from 20mers to 50mers! EOM

Posted by Mark W. on August 30, 2001 6:50 AM

Re: Thanks Mark! I hope we don't go from 20mers to 50mers! EOM

Well, that would require 90 more columns. They're
available (with 100 or so to spare), but at that
point you might want to consider creating a custom
function. : I guess you wanted to view the randomized results