CODEWITHSUNDEEP
arraysexcelexcel-formulabooleanquery
Joe North
Joe North

Reputation: 1

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

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.

Upvotes: 0

Views: 236

Answers (2)

Ike
Ike

Reputation: 13054

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))

It will return ("A" and "B") OR ("A" and "C") OR ("A" and "D") OR ("B" and "C") OR ("B" and "D") OR ("C" and "D") for A to D in A1:A4.

Upvotes: 1

Jos Woolley
Jos Woolley

Reputation: 9062

Taking your request literally:

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

Upvotes: 1

Related Questions