How to create Google Sheet app that retrieves a mix of random words

Question

I have a Google Sheet with a lot of columns, each of them containing words from a category. I would love to create a formula that can retrieve random words and put them together. For example:

Colum 1 [cute, fav, dear, great]
Column 2 [love, friends, family, peers]

With the formula you get a random mix e.g. "cutelove", "favpeers", "greatlove", etc.

My knowledge is pretty basic, so I would love to know if this is even doable or at least where could I start. Really appreciate the help.

Answer 1

you could use INDEX() and RANDBETWEEN(), see if this example satisfies you

Answer 2

If there are not too many categories to join, you can generate all possible combinations like this:

=arrayformula( 
  flatten( 
    filter(A2:A, len(A2:A)) 
    & " " & 
    transpose(filter(B2:B, len(B2:B)))
  ) 
)

See column G in the sample spreadsheet. This is a simple Cartesian product.

To get 10 random word pairs, use sortn(), like this:

=sortn( 
  flatten( 
    filter(A2:A, len(A2:A)) 
    & " " & 
    transpose(filter(B2:B, len(B2:B)))
  ), 
  10, 0, 
  randarray(counta(A2:A) * counta(B2:B)), 
  true  
)

See column I in the sample spreadsheet.

If your intention is simply to generate a list of word pairs where no word is re-used, shuffle the two columns like this:

=sort(A2:A, randarray(rows(A2:A)) * not(isblank(A2:A)), false)

=sort(B2:B, randarray(rows(B2:B)) * not(isblank(B2:B)), false)

See columns K:L in the sample spreadsheet.