1

Example:

Is there a formula to list all possible 2 card combinations of 4 playing cards?

4 playing cards:

A♥
A♦
A♣
A♠

The result should be 6 combinations only (Not 4 x 4 = 16 Combinations):

1: A♥ A♦
2: A♥ A♣
3: A♥ A♠
4: A♦ A♣
5: A♦ A♠
6: A♣ A♦
0

=ARRAYFORMULA(QUERY(IF(QUERY(QUERY(SPLIT(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A4&CHAR(9)), COUNTA(A1:A4)), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(A1:A4&CHAR(9), COUNTA(A1:A4))), CHAR(9)))," "), 
 "where Col1 <> Col2", 0), "select Col1", 0)<
 QUERY(QUERY(SPLIT(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A4&CHAR(9)), COUNTA(A1:A4)), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(A1:A4&CHAR(9), COUNTA(A1:A4))), CHAR(9)))," "), 
 "where Col1 <> Col2", 0), "select Col2", 0), 
 QUERY(SPLIT(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A4&CHAR(9)), COUNTA(A1:A4)), CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(A1:A4&CHAR(9), COUNTA(A1:A4))), CHAR(9)))," "), 
 "where Col1 <> Col2", 0), ), "where Col1 <>''", 0))

0

  • Thank you very much! That works perfectly for 4 cards. I also tried 52 cards but I get the error message: Error - Text result of REPT is longer than the limit of 32000 characters. Would you know how to overcome for this case? – where_theres_a May 17 at 12:15
  • can you share a copy of your sheet? – user0 May 17 at 12:17
  • Hey @user0, are you available to help me with another formula? I explained what I am looking to do in this sheet here: docs.google.com/spreadsheets/d/… Thank you in advance :) – where_theres_a May 18 at 13:32

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.