let's asume I have two lists:

(1)

• plays baseball
• plays cricket
• plays tennis
• plays golf
• plays rugby

(2)

• Tim
• Steve
• Max

Now I would like to make a random, but mostly even distribution in this way:

• Tim plays rugby
• Steve plays tennis
• Tim plays baseball
• Max plays cricket
• Steve plays golf

I would like to avoid that e.g. Tim plays 3 or 4 sports and e.g. Max does not play any.

// EDIT: I would like to distribute the whole of List (1). So the absolute number of distributions is the length of list (1).

Is there a possibility of doing this in google sheets?

Best, Florian

CodePudding user response：

Say your noun phrases are in column A and verb phrases are in column B. Empty cells are allowed.

In column C, every new sentence can be generated by

= join(" ",
       index(filter(A:A,A:A<>""),randbetween(1,counta(A:A)),1),
       index(filter(B:B,B:B<>""),randbetween(1,counta(B:B)),1))

"Drag", or spread, the formula as much as you need.

Note that non-empty cells with empty text are not ignored.