Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
random | Excel Worksheet Functions | |||
Random name | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) | |||
How do I find random number in list of random alpha? (Position is. | Excel Discussion (Misc queries) |