How to select random cell value from multiple columns without duplicates

[falguthmy]

Hello.

I have 7 columns (groups) of data.

I'd like to randomly select a cell value from any of the groups several times (say, 5 times) without duplicates.
The results should be categorised into their respective groups i.e. if cell B8 was the random selection and is from group 1, the result should be displayed in group 1 in another table (see sample file).

What would the formula look like?

Thanks for your help!

[Phuocam]

Try this in B14:

Please Login or Register  to view this content.

[falguthmy]

The formula only selects a random cell from one column and not all columns. Also, if the random cell is from, say, cell F3, then the result would need to show in cell F14 only since it is from group 3. All the other cells in row 14 would be blank.

Hope this makes more sense.

[Phuocam]

Hi,

See attached file

[falguthmy]

Hi and thanks for the swift response.

This looks splendid! It seem to work for me. If you don't mind, could you explain how you came up with the formula used in A14:A18?

Thank you again.

[falguthmy]

I also noticed in the formula you locked $A$13:A13.. which I didn't quite understand..
For example in cell A14 the formula is:
=SMALL(IF(COUNTIF($A$13:A13,ROW(INDIRECT("1:"&$M$11)))=0,ROW(INDIRECT("1:"&$M$11)),""),RANDBETWEEN(1,$M$11+1-ROWS($A$13:A13)))

Why $A$13:A13? This cell points to a header. Please elaborate.

[Phuocam]

You can split the formula to see it.

Or try

in A14: ="group "&RANDBETWEEN(1,7)

in B14: =IF($A14=B$13,INDEX(B$2:B$11,MATCH(LARGE(A$2:A$11,COUNTIF($A$14:$A14,$A14)),A$2:A$11,0)),"")

[falguthmy]

Thank you very much for the great feedback.