Generate (concat) sentences from three columns - each column has multiple words in one cel

I have an excel sheet.

Column B1 has a horizontal list of words (people) separated by strings in the same cell.
Column C1 has a horizontal list of words (verbs) separated by strings in the same cell.
Column D1 has a horizontal list of words (places) separated by strings in the same cell.

I would like to iterate through and create sentences.
Example: My daughter is playing football in the garden.

a. I would like to produce 100 sentences by concatenating one word from each column.
b. The sentences should not be the same unless it's unavoidable.

I have tried this formula but it doesn't work on my macbook pro (things never do that's why I'm here.

=INDEX(SPLIT(B1, ","), RANDBETWEEN(1, COUNTA(SPLIT(B1, ",")))) & " " &
INDEX(SPLIT(C1, ","), RANDBETWEEN(1, COUNTA(SPLIT(C1, ",")))) & " " &
INDEX(SPLIT(D1, ","), RANDBETWEEN(1, COUNTA(SPLIT(D1, ","))))

^^ same as above ^^

Really sorry.

We see #REF! errors now, maybe you need to save the data as values before uploading. You can also update your original post with the file and just let us know you have updated that. That way there will not be lots of files through the post.

I'm a bit confused. What is the SPLIT function? Is this a Googlesheets question?

is 16.37 for Mac a version of the Product: O365 for Mac or something else? I have no idea what 16.37 means....

With TEXTSPLIT

=TRIM(REDUCE("",B2:D2,LAMBDA(a,v,VSTACK(TOCOL(a&" "&TEXTSPLIT(v,","))))))

Without TEXTSPLIT

=LET(z,B2:D2,l,LEN(z),n,l-LEN(SUBSTITUTE(z,",",))+1,p,PRODUCT(n),s,SEQUENCE(p,,0),m,MOD(INT(s/p*INDEX(n,1)*INDEX(n,2)^{0,1,1}*INDEX(n,3)^{0,0,1}),n),
t,TRIM(MID(SUBSTITUTE(z,",",REPT(" ",l)),m*l+1,l)),INDEX(t,,1)&" "&INDEX(t,,2)&" "&INDEX(t,,3))

I took a slightly different approach. Instead of returning ALL possible sentences, this returns n sentences (selected in cell B4), chosen randomly from all possible selections. A new selection can be made by changing the seed number in B5 (any integer 1-1,000,000).

Certainly not as neat as Bo_Ry's effort, but here it is, for what it's worth!

=LET(Seed,B5,N,B4,A,TEXTSPLIT(B2,,","),B,TEXTSPLIT(C2,,","),C,TEXTSPLIT(D2,,","),D,ROWS(A),E,ROWS(B),F,ROWS(C),G,SEQUENCE(D*E*F)-1,H,INDEX(A,1+INT(G/(E*F)))&" "&INDEX(B,1+MOD((1+INT(G/F))-1,E))&" "&INDEX(C,1+MOD(G,F)),I,MOD((B5+G)/PI(),1),INDEX(H,MATCH(LARGE(I,SEQUENCE(B4)),I,0)))

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

No need for a new thread. However, I must have misundeestood the requirement.

Mine generates n unique sentences, where you define n.

If that's not what you want... you'll have to explain again.

No duplicates for 7 Places

=LET(z,B2:D2,d,DROP(REDUCE(0,z,LAMBDA(a,v,LET(t,TEXTSPLIT(v,,","),r,ROWS(t),HSTACK(a,VSTACK(r,SORTBY(t,RANDARRAY(r))))))),,1),
m,TAKE(d,1),BYROW(INDEX(d,MOD(SEQUENCE(MIN(m),,0),m)+2,SEQUENCE(,COLUMNS(d))),LAMBDA(c,TEXTJOIN(" ",,c))))


No duplicates for 16 verbs
Some duplicates for 16-12 = 4 people and 16-7 = 9 Places

=LET(z,B2:D2,d,DROP(REDUCE(0,z,LAMBDA(a,v,LET(t,TEXTSPLIT(v,,","),r,ROWS(t),HSTACK(a,VSTACK(r,SORTBY(t,RANDARRAY(r))))))),,1),
m,TAKE(d,1),BYROW(INDEX(d,MOD(SEQUENCE(MAX(m),,0),m)+2,SEQUENCE(,COLUMNS(d))),LAMBDA(c,TEXTJOIN(" ",,c))))

Try

=LET(z,B2:D2,d,DROP(REDUCE(0,z,LAMBDA(a,v,LET(t,TEXTSPLIT(v,,","),r,ROWS(t),HSTACK(a,VSTACK(r,SORTBY(t,RANDARRAY(r))))))),,1),
m,TAKE(d,1),BYROW(INDEX(d,MOD(SEQUENCE(100,,0),m)+2,SEQUENCE(,COLUMNS(d))),LAMBDA(c,TEXTJOIN(" ",,c))))

You have a European locale, so try this:

=LET(z;B2:D2;d;DROP(REDUCE(0;z;LAMBDA(a;v;LET(t;TEXTSPLIT(v;;",");r;FILAS(t);HSTACK(a;VSTACK(r;ORDENARPOR(t;MATRIZALEAT(r)))))));;1);
m;TAKE(d;1);BYROW(INDICE(d;RESIDUO(SECUENCIA(100;;0);m)+2;SECUENCIA(;COLUMNAS(d)));LAMBDA(c;UNIRCADENAS(" ";;c))))

TEXTSPLIT and some other functions are available in Mac: 16.65 (Build 220911) and later - you need to run an update on your Excel.

Originally Posted by codeBeast

I copied and pasted and got this error "The first argument of LET must be a valid name."

Who are you talking to?

Then open the attachment in my previous post and see if that works. If not, then at least ONE of the functions is not available for you.

And you need to update your profile, which says 16.37!

Please explain EXACTLY how mine DOES NOT meet your requirement.

No sentences are duplicated... ever. It generates all 12 x 16 x 7 = 1344 possible sentences and returns (randomly) n of them, where YOU define n.

There are ONLY 12 name phrases (My brother Bob, etc). If you generate 100 sentences, then, randomly, you can expect to get each name appearing about 100/12, approximately 9 times.

It is mathematically IMPOSSIBLE to generate more than 12 different starting phrases if there are only 12 possible starting phrases. Hence my request for you to explain clearly why my effort does not work.

I still do not understand what you want to see!!

To Glen

First sentences generated.

My friend Freddy is dancing in the disco in the mountain
My crazy cousin Franky is driving a car in the tree
My dangerous daughter is playing football in the house
My sunny son is eating a hamburger in the river
My friend Freddy is driving a car in the river
My crazy cousin Franky is playing football in the sky
My dangerous daughter is singing a song in the garden
My dangerous daughter is going to the toilet in the class
My friend Freddy is drinking a coca-cola in the class
My crazy cousin Franky is singing a song in the mountain
My dad is going to the toilet in the tree

Various people and verbs are repeated in the first 11 sentences for example.
I would prefer to have a unique "people" value and verbs value every 12 or so sentences.
So for example
Instead of using My dangerous daughter 3 times in the first block of sentences, I need some of the other that haven't been used: The little boy, The little girl etc...

Basically, because I will create activities using blocks of 10-12 sentences so i need them to be as unique as possible.

Quick edit - I found the value to generate 1000 sentences - just in case you read my post before this edit.

I have everything working with your formula. You were right and this works perfectly - generating a new person + unique verb phrase for quite a few rows.

THaNK YOU so much.