Creating a function to randomize a list of alphanumeric entries in an array

[Gabby]

Hi. I'm at a loss here. I tried using both the RAND and the RANDBETWEEN
analysis toolpack functions to do this but both of them fail to do what
I'm requiring.

I have eight columns of eight rows of data. I'd like to create a
function that can randomly select the criteria I've listed on the data
range. For example,

a1 b1 c1 d1 e1 f1 g1 h1
a2 b2 c2 d2 e2 f2 g2 h2
a3 b3 c3 d3 e3 f3 g3 h3
a4 b4 c4 d4 e4 f4 g4 h4
a5 b5 c5 d5 e5 f5 g5 h5
a6 b6 c6 d6 e6 f6 g6 h6
a7 b7 c7 d7 e7 f7 g7 h7
a8 b8 c8 d8 e8 f8 g8 h8

is the data I've listed I want used to generate a random listing of the
above data. The function should read the above data and spit out one of
the above entries in a different cell. In other words, the fuction will
read the array and choose which alphanumeric entry to input in a
different cell of my choosing. I type in the custom fuction and it will
look at the data above and choose at random which data from the criteria
to generate at random. Can this be done? Any help would be greatly
appreciated.

 

[immanuel]

Use this formula:

=INDIRECT(ADDRESS(1+INT(RAND()*8),1+INT(RAND()*8)))

To get a random sample from A1:H8.

/i.

 

[Vasant Nanavati]

Perhaps:

=INDEX(A1:H8,ROUNDUP(RAND()*8,0),ROUNDUP(RAND()*8,0))

 

[Max]

Assuming your data range is in Sheet1, A1:H8

one way using RANDBETWEEN
(provided duplicates is not an issue)

Put in any cell
: =OFFSET(Sheet1!$A$1,RANDBETWEEN(0,7),RANDBETWEEN(0,7),1,1)

Copy as desired

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email

 

[Gabby]

Your function works great. However, you're right about one tiny
thing...duplicates is an issue. I'd like it to generate the
alphanumerica numbers and not duplicate the entries it already randomly
selected. For example, if I copy the formula you create...it can
randomly select the same alphanumeric number it generates again. I'd
like the formula to generate a UNIQUE alphanumeric entry based on the
criteria array. Is there any way to do that?

 

[Max]

One possible way ...

Assuming your data range is in Sheet1, A1:H8 (as before)

1) Do this set-up in a new sheet, say in Sheet2:
--------------------------------------------------------
Number 1 to 64 down C1:C64

Put in D1: =Sheet1!A1
copy down to D8 (down 8 rows)

Put in D9: =Sheet1!B1
copy down to D16 (down 8 rows)

Put in D17: =Sheet1!C1
copy down to D24 (down 8 rows)

And so on. Repeat in a similar way until D164 are filled
(this provides the links to your data range in Sheet1, A1:H8)

Name the range C164 as : DataTBL

Put in B1: =RAND(), copy down to B64

Name the range B1:B64 as : RandTBL

Select A1:A64

Put in the formula bar: =RANK(RandTBL,RandTBL)

Array-enter the formula, viz.:
Hold down CTRL + SHIFT, press ENTER
(instead of just pressing ENTER)

Done correctly Excel will wrap curly braces around the formula:
{=RANK(RandTBL,RandTBL)}
(don't type-in the curly braces!)

Name the range A1:A64 as : RankTBL

-----------------------------------------------
2) With the set-up above done,

In any new sheet, say in Sheet3:
-------------------------------------

A. Generating Row-wise
------------------------------

Put in A1: =VLOOKUP(INDIRECT("Sheet2!A" & ROW()+COLUMN()-1),DataTBL,2,FALSE)

Copy A1 across as desired, say:
to E1, for 5 unique random selections from your data-table.
to J1, for 10 unique random selections from your data-table
and so on

You can generate up to a max 64 "row-wise" unique random
selections from your datatable (in A1:BL1)

Pressing F9 gives another random selection

B. Generating Column-wise
---------------------------------

Put in any cell, let's take A1: =VLOOKUP(Sheet2!A1,DataTBL,2,FALSE)

Copy A1 down as desired, say:
to A5, for 5 unique random selections from your data-table.
to A10, for 10 unique random selections from your data-table
and so on

Again, as before, you can generate up to a max 64 "column-wise"
unique random selections from your datatable (in A1:A64)

Pressing F9 gives another random selection

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------