Select random cell from non-adjacent cells/columns-CodePudding

I got multiple columns with information. I want to pick a random sentence from 'Mehrzahl' for every row involved. Is it possible to do this with a formula? In the case of Mehrzahl I want to select either E2 or N2 randomly, either E3 or N3 randomly, et cetera.

CodePudding user response：

you can flip a coin:

=IF(COINFLIP(); E2; N2)

if you want to pick one of each for the whole column:

=INDEX(IFERROR(SORTN(SORT(SPLIT(FLATTEN(IF({E2:E, N2:N}="",,
 {E2:E, N2:N}&"×"&ROW(A2:A)&"×"&RANDARRAY(ROWS(A2:A), 2))), 
 "×"), 2, 1, 3, 1), 9^9, 2, 2, 1)),,1)

CodePudding user response：

You can choose randomly between the value in E2 and N2 using rand() and if(), like this:

=if( rand() < 0.5, E2, N2 )