0

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.

1

2 Answers 2

0

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

1
  • This exactly what I was looking for, thank you! The only problem is I keep getting the same 4 random combinations. I'll keep exploring and trying new things from here. Thank you so much again, really helpful!
    – Alorve
    Mar 17 at 11:09
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.