excelexcel-formula

How to get 2D combination of words?


I have a list of words from A2:A5 like:

Color
Cyan
Magenta
Yellow
Black

I am trying to get a two words pair of combinations using Excel formula only. So, my desired output would be like the following:

Desired Resulr
Cyan Magenta
Cyan Yellow
Cyan Black
Magenta Cyan
Magenta Yellow
Magenta Black
Yellow Cyan
Yellow Magenta
Yellow Black
Black Cyan
Black Magenta
Black Yellow

I have tried the following formula-

=INDEX(A2:A5,MID(BASE(SEQUENCE(PERMUTATIONA(ROWS(A2:A5),2)),ROWS(A2:A5),2),SEQUENCE(1,2),1)+1)

Which gives me the result:

Cyan Magenta
Cyan Yellow
Cyan Black
Magenta Cyan
Magenta Magenta
Magenta Yellow
Magenta Black
Yellow Cyan
Yellow Magenta
Yellow Yellow
Yellow Black
Black Cyan
Black Magenta
Black Yellow
Black Black
Magenta Cyan

Here "Magenta, Magenta", "Yellow, Yellow", "Black, Black" are coming which is not expected.


Solution

  • Here is one way you could try:

    enter image description here

    =LET(
         _a, A2:A5,
         _b, TOROW(_a),
         _c, TOCOL(IF(_a=_b, NA(), _a&"|"&_b), 2),
         TEXTSPLIT(TEXTAFTER("|"&_c, "|", {1,2}), "|"))
    

    Alternatively, bit shorter version of the above, and without Text Group Functions:

    =LET(
         _a, A2:A5,
         _b, TOROW(_a),
         _c, TOCOL(IF(_a=_b, b, _a), 2),
         _d, TOCOL(IF(_a<>_b, _b, a), 2),
         HSTACK(_c, _d))
    

    So, I took a look at the formula you shared in the OP, and I figured out why it's giving the same or duplicate combos. Made a few changes to fix it up and got it working right, updated version! Try changing the last variable _f to any other to understand the reason, or you could evaluate your own formula as well!

    =LET(
         _a, A2:A5,
         _b, ROWS(_a),
         _c, MID(BASE(SEQUENCE(PERMUTATIONA(_b, 2)), _b, 2), SEQUENCE(, 2), 1)+1,
         _d, UNIQUE(_c),
         _e, FILTER(_d, CHOOSECOLS(_d, 1)<>CHOOSECOLS(_d, 2)),
         _f, INDEX(_a, _e),
         _f)
    

    Per OP's Comment:

    Your filtering concept is excellent in last formula formula. Last formula is scalable to make pairs of 3 and 4 colors. However, when I tried to generate all possible combinations of 4 colors (it would be 256 from this function PERMUTATIONA(4,4)) then it is generating 1 duplicate row. I am trying to solve that duplicate issue. Otherwise your last formula is accepted to me for current question. Any thought on all permutation combination of 4 colors?

    Partial Data Screenshot:

    enter image description here

    =LET(
         _a, A2:A5,
         _b, ROWS(_a),
         _c, MID(BASE(SEQUENCE(PERMUTATIONA(_b, _b), , 0), _b, _b), SEQUENCE(, _b), 1)+1,
         INDEX(_a, _c))
    

    And without dupes by row:

    =LET(
         _a, A2:A5,
         _b, ROWS(_a),
         _c, MID(BASE(SEQUENCE(PERMUTATIONA(_b, _b),,0), _b, _b), SEQUENCE(, _b), 1)+1,
         _d, BYROW(_c, LAMBDA(_x, OR(_x>TOCOL(_x)))),
         FILTER(INDEX(_a, _c), _d, "Oops Not Found!"))