Picking multiple values randomly without repetition, into a single cell.

[empk]

I have three ranges (B1:D6) filled with numbers. I would like to pick at least 3 numbers randomly without repetition from the given ranges into the cells F3:F5. This has to be based on the category of the ranges given in cells (E3:E5) viz., Hard, Easy, Moderate. The cells F3, F4 & F5 should be populated with minimum of 3 values from the desired range respectively.

A B C D E F
1 Hard Easy Moderate
2 1 2 11
3 3 4 13 Hard 1, 8, 3
4 6 5 14 Easy 2, 10, 5
5 8 7 19 Moderate 13, 19, 11
6 9 10 21

How can I achieve this? Pl help.

 

[XOR LX]

Hi,

Do you have Excel 2016 with an Office 365 subscription? If so, I can propose a solution using the worksheet formula TEXTJOIN. Otherwise, I think you will need some VBA.

Regards

 

[Peter Bartholomew]

These are overkill!
The first is a combination problem of 6 from 50 without replacement.
The second is a random permutation of 6 objects without replacement.

The combination problem handles exclusion by generating values in a strictly descending sequence.

 

[empk]

Hi,

Do you have Excel 2016 with an Office 365 subscription? If so, I can propose a solution using the worksheet formula TEXTJOIN. Otherwise, I think you will need some VBA.

Regards

Yes I have Excel 2016. Pl provide the solution.

 

[empk]

These are overkill!
The first is a combination problem of 6 from 50 without replacement.
The second is a random permutation of 6 objects without replacement.

The combination problem handles exclusion by generating values in a strictly descending sequence.

I went through your files and the code in there, has gone all above me. If you can explain how it is achieved, may be it will be of some help to me.Thanks.

 

[Peter Bartholomew]

Was it the math or the Excel implementation that beat you?

There is a conceptually much simpler approach that requires you to assign a random number to each option using a helper range. The solution returned comprises the options flagged with the three smallest random numbers.
(@Hui has posted showing the method in the past)

I didn't use that approach in my workbook because I was selecting from 50 numbers (requiring 50 random numbers for each draw) and, given that I calculate over 1000 possible results each time F9 is pressed, I decided I did not want to make space for over 50 000 random numbers!

The method I used works by calculating the number of combinations remaining given each possible selection. The more options the more likely the calculation will select the number.

 

[XOR LX]

Yes I have Excel 2016.

With an Office 365 subscription, as I originally asked? I need to know that you have access to the TEXTJOIN function (which you can also verify by attempting to use that function within a workbook).

Also, I should have said that the single-cell, non-VBA set-up I have designed is necessarily resource-heavy: it will work for the sample data you provide (5 rows' worth of data), though has a theoretical limit of 20 rows' worth of data and, in practice, will struggle if the number of rows to be considered is more than 15.

If this is still of interest to you then please let me know.

Regards

 

[Peter Bartholomew]

I am not sure whether I have fully understood the requirement.
The attached returns some values at random from a user-selected list.
Is that what is needed?

The grey calculation cells can be eliminated either by using named formulae or nesting partial calculations within the final result. The green helper cells cannot be bypassed in this manner.