Random Selection

[carl]

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.

 

[Lars-Åke Aspelin]

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((ROW()-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((ROW()-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

 

[Max]

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),"",INDEX(A:A,SMALL(D,ROWS($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.

 

[Lars-Åke Aspelin]

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),"",INDEX(A:A,SMALL(D,ROWS($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.

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.

 

[Chris Campanile - National Search]

Carl,

Have you tried a Pivot Table? I imagine you might have already done this,
but I figured I would suggest it anyway. This might be a simple solution.
Check your HELP in Excel to see how to create one.


Chris Campanile
National Search Consultant
Chris Campanile National Search, LLC
www.ChrisCampanile.com

1450 South Street
Castle Rock, CO 80104
Phone: 720-524-3473
Fax: 720-554-7753
(e-mail address removed)

I help companies hire Big-4 Audit and Tax talent across the Nation.

 

[Max]

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.

 

[Bernd P]

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

 

[Mahesh]

I have also same kind of problem? I really need it. Is there anyone who can
help us