On Thu, 20 Mar 2008 12:20:01 -0700, carl
wrote:

I have a table like this (actually much larger):

Stock Ticker
AAPL VAAAH
AAPL VAAMH
AAPL VAAAJ
AAPL VAAMJ
AAPL VAAAL
AAPL VAAML
AAPL VAAAN
AAPL VAAMN
AAPL VAAAP
C C OC
C C FC
C C RC
C C CR
C C OR
C C FR
C C RR
C C IE
C C UE
C C IS
C C US
C C IF
C C UF
C C IR
C C UR
C C ID


I would like to create a table like this:

Stock Ticker

AAPL VAAAJ
AAPL VAAMN
C C UE
C C IE


Where the entries in the table represent "N" (in this case 2) randomly
selected Stock-Ticker Pairs. I need the table to list out "N" of these
selections for each Stock in the original table. So, in the original table
there are 2 stocks (AAPL and C) thus the new table has 2 selections for AAPL
and 2 selections for C.

I hope I explained my problem clearly.

Thank you in advance.


Assuming your original table is in columns B and C, try the following:

In Cell A2: =RAND()
Copy down cell A2 to "cover" the same number of rows as in your
original table

In D2: =D1+IF(B2<B1,1,0)

In cell E2:
=IF(ISERROR(MATCH(ROW()-1,$D:$D,0));"";MATCH(ROW()-1,$D:$D,0)

In cell F2:
=IF(ROW()N*(MAX($D:$D)-1)+1,"",LARGE(OFFSET(A$2,OFFSET($E$2,INT((ROW()-2)/$G$1),0)-2,0):OFFSET(A$2,OFFSET($E$2,INT((ROW()-2)/N)+1,0)-3,0),MOD(ROW()-2,N)+1))
(N is the name of the range/cell where you have your "N")

In cell G2:
=IF(ROW()N*(MAX($D:$D)-1)+1,"",VLOOKUP($F2,OFFSET($A$2,OFFSET($E$2,INT((R OW()-2)/N),0)-2;0):OFFSET($C$2,OFFSET($E$2,INT((ROW()-2)/N)+1,0)-3,0),2,FALSE))
(N is the name of the range/cell where you have your "N")

In cell H2:
=IF(ROW()N*(MAX($D:$D)-1)+1,"",VLOOKUP($F2,OFFSET($A$2,OFFSET($E$2,INT((R OW()-2)/N);0)-2;0):OFFSET($C$2,OFFSET($E$2,INT((ROW()-2)/N)+1,0)-3;0),3,FALSE))
(N is the name of the range/cell where you have your "N")

Copy down cells D2 to H2 to "cover" the same number of rows + 1 as in
your original table (Note: The +1 is essential for column D)

Finally enter "Stock" in cell G1 and "Ticker" in cell H1.

Your new table is in columns G and H and you may hide the columns that
you don't want to see.

Hope this helps / Lars-Åke