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.

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.

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.