1

I have a list of items that looks like this :

My list of items

The goal would be to get every unique combination of 4 of those items.
The combinations are unordered so ABCD, BADC, DBAC, etc. are considered the same result and only one of them should appear.

With this example, the result should look like this :

The desired output

With only 5 items it's quite easy to calculate in your head, but my real project has 18 of those items, which means there is 3060 combinations ...

The ideal output would be 1 item per column but this isn't that important as I can still separate them later if needed.

I don't mind having to setup a new sheet only for the calculations.

Improve this question
4
  • Welcome. We already have related questions like Show all combinations for a selection of columns. If you need further help, please add a brief description of you search/research efforts as is suggested by How to Ask.
    – Rubén - Volunteer Moderator - ♦
    Oct 25, 2019 at 15:24
  • All related questions I could find were about several sets of items in several columns, with items from the same column not mixing up with each others. My question is literally the opposite thing.
    – Runj
    Oct 25, 2019 at 16:36
  • Have you learned something of those questions that you already read? What kind of solution are you looking for, one formula based or one based on using a script?
    – Rubén - Volunteer Moderator - ♦
    Oct 25, 2019 at 16:39
  • I'd be more looking for a formula based one
    – Runj
    Oct 25, 2019 at 16:41

1 Answer 1

Reset to default
1

Formula

=ArrayFormula(SPLIT(REPLACE(IFERROR(ROW(1:5)/0,)&A1:A5&","&JOIN(",",TRANSPOSE(A1:A5)),ROW(1:5)*2,2,""),","))

How it works

The core logic is to create a transposed copy of the values of A1:A5 for each element of A1:A5 then remove the first element for the first copy , the second element of the second copy, and so on, then join A1:A5 with the corresponding copy.

The formula use the concatenate operator (&), the multiplication operator (*), division operator (/),TRANSPOSE, ROW, JOIN, SPLIT, REPLACE,IFERROR and ARRAYFORMULA.

NOTES

If your real values use commas, on each instance of "," replace the comma by another character (it could be an emoji or any other Unicode character)

If the formula will be placed in a row that is not the first one, then you should use the addition operator + to compensate the position.

If the real values have more than one character, the number 2 in the formula should be replaced by another value.

Related

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.