I have a list of items that looks like this :
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 :
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.
Best Answer
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 ofA1:A5
then remove the first element for the first copy , the second element of the second copy, and so on, then joinA1:A5
with the corresponding copy.The formula use the concatenate operator (
&
), the multiplication operator (*
), division operator (/
),TRANSPOSE
,ROW
,JOIN
,SPLIT
,REPLACE
,IFERROR
andARRAYFORMULA
.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