random 3 out of many
 

Hi all,

In column A I have in each cell one alfphanumerical item, the lenghts
differ.
In column B should get in each cell, from B1 down, 3 randomly picked items
of column A (format: item, item, item) without any duplicates (each A-item
only once in B).
Column A could contain a few hundred items, so a macro would be handy, but I
can't figure out how.
Your help will be appreciated.

Jack Sons
The Netherlands

random 3 out of many
 

See if the following formula helps, and anter entering the formula copy it
down 2 cells to get you 3 random samplings. Modify $A:$A to indicate the
column your data is in for the INDEX function, and the range $A2:$A397 in the
COUNTA function to match your data range:

=INDEX($A:$A,RANDBETWEEN(1,COUNTA($A2:$A397)),RAND BETWEEN(1,1))
--
Kevin Backmann


"Jack Sons" wrote:

Hi all,

In column A I have in each cell one alfphanumerical item, the lenghts
differ.
In column B should get in each cell, from B1 down, 3 randomly picked items
of column A (format: item, item, item) without any duplicates (each A-item
only once in B).
Column A could contain a few hundred items, so a macro would be handy, but I
can't figure out how.
Your help will be appreciated.

Jack Sons
The Netherlands

random 3 out of many
 

Kevin,

Thanks. But I see duplications because the formula can't see wether an
A-item is already used, I think. Also, in B1 B2 B3 etc I should
get (for instance)

B!: abc2T, kkl5, new66ork (these could be for instance A66, A108, A2)
B2: 123Kils, 2B5, uk3uk (these could be for instance A234, A99,
A12)
B3: klAS, 88H99, 2398Y (these could be for instance A36, A127,
A431)
B4 ...

Each A-item not to be used again in the B-cells.

Can you help me further?

Jack.

"Kevin B" schreef in bericht
...
See if the following formula helps, and anter entering the formula copy it
down 2 cells to get you 3 random samplings. Modify $A:$A to indicate the
column your data is in for the INDEX function, and the range $A2:$A397 in
the
COUNTA function to match your data range:

=INDEX($A:$A,RANDBETWEEN(1,COUNTA($A2:$A397)),RAND BETWEEN(1,1))
--
Kevin Backmann


"Jack Sons" wrote:

Hi all,

In column A I have in each cell one alfphanumerical item, the lenghts
differ.
In column B should get in each cell, from B1 down, 3 randomly picked
items
of column A (format: item, item, item) without any duplicates (each
A-item
only once in B).
Column A could contain a few hundred items, so a macro would be handy,
but I
can't figure out how.
Your help will be appreciated.

Jack Sons
The Netherlands