mercredi 8 juin 2022

Randomly select multiple values from a list based on a TRUE filter in Excel

Summary

I would like to select multiple randomly selected options from a list within Excel, but filtered to only include those with a TRUE indicator in another column. The number of random selections to make will need to be vary. Additionally, duplicates shouldn't be pulled. Ideally, I would like to have the formula contained within one cell, and have it spill to the required length, if such a thing would be possible.

Current attempt

I have tried making this logic work through the combined used of =INDEX, =RANDBETWEEN =FILTER and a helper =RAND() column, in an attempt to filter the data, then randomly select from it. The issue I'm finding is that =FILTER does not like to combine properly (although I'm likely doing something wrong). Additionally, this involves copying the formula down to each cell I'd like a new randomly selected option for, which isn't ideal if possible.

Example of required output

ID T/F Number to randomly select? Output
1 TRUE 5 2 [Formula is here and spill down]
2 TRUE 5
3 FALSE 7
4 FALSE 9
5 TRUE 12
6 FALSE
7 TRUE
8 TRUE
9 TRUE
10 FALSE
11 TRUE
12 TRUE

Here is an example of how I'd ideally like the output of the data. The data itself is in A1:B13. I'd enter the number of options to randomly select in C2, then the output would be generated based on a solution contained in D2, which spills down accordingly. The requirements that it only selects from an ID population in column A where the TRUE/FALSE indicator from column B for that ID is TRUE. I'm looking for the solution to this question to be the formula I'd need to put into D2, please.

Extra detail

I'm always looking to improve, so any explanation on how something works is always appreciated, so I don't end up asking the same things again!

I am using MSO 365, but I am restricted to version 2108 due to rollout schedules.

Thanks for any help with this!




Aucun commentaire:

Enregistrer un commentaire