0

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.

Ike
  • 9,580
  • 4
  • 13
  • 29
Joe North
  • 1
  • What is your intended result or usage for such as result? There are a number of ways to accomplish this, including setting up a helper column that "translates" the ABCD values into numbers and "adds" them in a way to calculate the result you're looking for. – PeterT Oct 06 '22 at 11:57

2 Answers2

1

Taking your request literally:

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

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
  • Awesome, thanks Jos - if I wanted to modify it to include x number of those terms, like ("A" AND "B" AND "C") where x = 3, how would I do that? – Joe North Oct 07 '22 at 10:46
1

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.

Ike
  • 9,580
  • 4
  • 13
  • 29