How to create boolean queries on excel including two or more items in a list?-CodePudding

I'm trying to create a boolean query in excel that will include every variation of any two items in a list.

To demonstrate, let's say my dataset is:

A
B
C
D

I'd like to create a formula which outputs ("A" AND "B") OR ("A" AND "C") OR ("A" AND "D") OR ("B" AND "C") OR ("B" AND "D") OR... etc. Its not a big deal if it includes, for example ("A" AND "B")... ("B" AND "A").

My dataset is much bigger than four so it's extremely time consuming to do this manually.

CodePudding user response：

Taking your request literally:

=LET(ζ,A1:A4,ξ,ROWS(ζ),TEXTJOIN(" OR ",,IF(SEQUENCE(ξ)<SEQUENCE(,ξ),"("&ζ&" AND "&TRANSPOSE(ζ)&")","")))

CodePudding user response：

Another solution :

=LET(data,A1:A4,
     cnt,COUNTA(data),
     m,MAKEARRAY(cnt,cnt,LAMBDA(r,c,IF(r<c,"("&INDEX(data,r)&" and "&INDEX(data,c)&")",""))),
     TEXTJOIN(" OR ",TRUE,m))