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