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.
Here is one way you could try:
=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:
=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!"))