0

Say I have a set of seven unique trading cards and I want to shuffle them and list them out for all their possible permutations (pool of 7, taking 7). No card may be duplicated within each permutation of an arrangement as I have only one of each card, and I am not looking for the number of permutations available. I want the list itself.

How would I use one Google Sheets formula (no manual data entry on an iterative basis past the first line) to shuffle these cards: A, B, C, D, E, F, and G into a list of unique permutations per row? They can be arranged into a tidy list (ABCDEFG) within each cell or each card to a column, but along each permutation, there must only be one of each card (ABACDEF shouldn't exist).

I've looked everywhere to shuffle just one column of values into all possible ordered sets with no repeated objects per set. Still, I lack the remembered terminology to search for the right answer.

If you know about the solution, give me the link please.

Arnav
  • 169
  • 8
dpcollier128
  • 61
  • 6
  • That's 5k results you're looking for. Memory and computation restrictions will apply. – TheMaster Sep 17 '22 at 23:52
  • Your question can be greatly improved if you add a table with sample input and output to the question. [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455/) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Sep 18 '22 at 09:54

1 Answers1

0

https://webapps.stackexchange.com/questions/129778/is-there-a-formula-to-list-all-possible-2-card-combinations-of-4-playing-cards-i

Reducing the question to trading cards for this post inspired me to pose it that same way to Google, and I found this. I believe the above example may be scaled up to 7 in 7 cards.

Feel free to try coming up with a better answer... I noticed the formula in the above post is rather long. I'm looking for the most compact answer available.

EDIT: Aha! I think I found one better.

Generate all possible combinations for Columns(cross join or Cartesian product)

I was having issues running into the rept function's 32000 character limit. So, I added the lengthy transposition formula found here:

https://stackoverflow.com/a/73523983/4143583

Combining that formula within a query similar to the first link's answer but scaled up a bunch ("where Col1 <> Col2 and Col2 <> Col3", etc.) seems to have solved my issue with repeated members within each permutation.

dpcollier128
  • 61
  • 6
  • If you just found this post looking for a solution to something, go here for some brilliant help (not by me) on a more complex problem: https://stackoverflow.com/questions/73752135/google-sheets-the-resulting-array-was-too-large-error-returned-by-a-query-eva/73752797?noredirect=1#comment130234802_73752797 – dpcollier128 Sep 17 '22 at 08:26
  • https://webapps.stackexchange.com/questions/124985/cartesian-product-with-constraints/125018#125018 – player0 Oct 19 '22 at 10:08