|
Random Selection
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. |
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 |
Random Selection
Another play to tinker with
Source data in cols A and B, from row2 down In C2 is a DV to select the N inputs required, eg: 1,2,3,4,5 (say) In D2: =IF(COUNTIF($A$2:A2,A2)$C$2,"",ROW()) In E2: =IF(ROWS($1:1)COUNT(D:D),"",INDEX(A:A,SMALL(D:D,R OWS($1:1)))) In F2: =IF(E2="","",INDEX(B:B,MATCH(E2,A:A,0)+RANDBETWEEN (0,COUNTIF(A:A,E2)-1))) Select D2:F2, copy down to the last row of source data. Minimize/hide away col D. Cols E & F will return the desired results. Press F9 to regenerate randomized results in col F. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "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. |
Random Selection
On Thu, 20 Mar 2008 17:31:02 -0700, Max wrote:
Another play to tinker with Source data in cols A and B, from row2 down In C2 is a DV to select the N inputs required, eg: 1,2,3,4,5 (say) In D2: =IF(COUNTIF($A$2:A2,A2)$C$2,"",ROW()) In E2: =IF(ROWS($1:1)COUNT(D:D),"",INDEX(A:A,SMALL(D:D, ROWS($1:1)))) In F2: =IF(E2="","",INDEX(B:B,MATCH(E2,A:A,0)+RANDBETWEE N(0,COUNTIF(A:A,E2)-1))) Select D2:F2, copy down to the last row of source data. Minimize/hide away col D. Cols E & F will return the desired results. Press F9 to regenerate randomized results in col F. This will allow the same "Stock"/"Ticker" pair to be shown more than once in the resulting table as the RANDBETWEEN function can generate the same result more than once. If that is not wanted I guess you have to add some more logic to this otherwise very nice solution. |
Random Selection
Agreed. Perhaps the earlier should have carried the caveat on randbetween's
limitation. Albeit the possibility of repeats occuring would lessen with increasing ranges. Anyway, I took advantage that OP did not state that the randomization was to be non-repeats explicitly. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lars-Åke Aspelin" wrote: This will allow the same "Stock"/"Ticker" pair to be shown more than once in the resulting table as the RANDBETWEEN function can generate the same result more than once. If that is not wanted I guess you have to add some more logic to this otherwise very nice solution. |
Random Selection
Hello Carl,
Are you really sure that if you have 5 stocks that you can extract at least 5 different from each of them? My approach would be something like: 1. List all unique stocks with counts of their tickers (lfreq: http://www.sulprobil.com/html/listfreq.html) 2. Select ROWS(lfreq(stocks)) uniqrandint tickers for each stock (uniqrandint: http://www.sulprobil.com/html/uniqrandint.html). Use INDEX etc. Regards, Bernd |
Random Selection
I have also same kind of problem? I really need it. Is there anyone who can
help us "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. |
| All times are GMT +1. The time now is 06:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com