Extract randomly data according to multiple criteria
I have the following sheet
My goal is to return data in col P randomly from col N or from col O. If cell B3 contains "2 shifts", it should return from col N and if cell B3 contains "3 shifts", it should return from col O. The amount of random cells to be returned is in D3 and should be able to be changed as wanted. I can extract cells in order by using the below.
=ArrayFormula(QUERY({N2:N, RANDARRAY(ROWS(N2:N))}, "SELECT Col1 LIMIT "&$D3))
The above only returns the first 6 but only contains 1 criteria. I cannot make it work. Please help PS: I can only use formulas and not scripts.
Solution
Try:
=LET(
arr, TOCOL({IF(B3 = "2 shifts", N2:N, O2:O)}, 1),
SORTN(arr, D3, , RANDARRAY(ROWS(arr)), )
)
- Codeigniter parentheses in dynamic Active Record query
- How to send cart data in hidden field and fetch it in codeigniter controller
- Sorting an array of objects by a property
- Sorting objects in array
- Codeigniter Insert Array to Database
- How to display an array of values in a table with javascript
- C# ToArray conversion merged into one line with resulting array element change
- accessing values in an ARRAY STRUCT in Google Bigquery
- Sort an object array by a column
- Push a new object into a sorted objectArray at the correct position to avoid re-sorting
- How do you natsort() by a column of an array of objects?
- How to create JSON Schema for an array of dictionaries with different type values
- Naturally sort a flat array without preserving original keys
- Create array of values from a dictionary of array of objects filtered by property
- How can I sort an alphanumerical Multidimensional Array in php?
- Naturally sort the rows of a 2d array by a column of alphanumeric values
- Sort an array of alphanumeric strings naturally
- Sort an array of strings by their 3rd character, then 4th, then 2nd
- Sort array based only on trailing numbers in strings
- Custom sort a flat array of scalar values so that integers are given top priority
- sort() prints 1 instead of the sorted array
- How to naturally sort a flat array of filenames (some ending with numbers, e.g. "-2")?
- Is there a shortcut way to return an array sorted in C#
- How to implement multiple custom sorting rules based on hyphen-delimited substrings?
- How can I access data of object stored in an array?
- How to include OR in Array formula where ONLY SOME criteria need to be TRUE - Excel
- How to fix 'Private field must be declared in an enclosing class'
- How can I write a Zig function that can accept and return strings?
- Search for groups of cells that contain values, calculate the difference, place result in adjacent column
- Including more commands if CSV column is not empty