Random Selection
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
|