Random Selection

C

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.
 
L

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.
Click to expand...


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
 
M

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

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:D),"",INDEX(A:A,SMALL(D: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.
Click to expand...

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.
 
C

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.
 
M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word Error Message: This file is corrupt and cannot be opened 1
select value of a random cell 1
Randomize records then select TOP (x) 7
Query to random select top N values 1
How to recognize shading and border formatting in the following code? 9

Top