@ > Home > Contents > List of combinations from a set of entries Excel 97+

To show all N^2 combinations (order is important, and repetition has to be counted) works like this:

D1: =INDEX(x,MOD(ROW()-1,N)+1)
E1: =INDEX(x,ROW()/N+1-1%%%)

mit x: =A1:A5 und N: =COUNT(\$A:\$A)

But how do you create a list without repetition and no entry doubling? N*(N-1)/2 entries have to be shown in B:C:

```        A   B   C   D   E
1   1   4   5   1   1
2   2   3   5   2   1
3   3   3   4   3   1
4   4   2   5   4   1
5   5   2   4   5   1
6       2   3   1   2
7       1   5   2   2
8       1   4   3   2
9       1   3   4   2
10       1   2   5   2
11               1   3

```

Solution:

B1: =INDEX(x,N-ROUND((2*ROW())^0.5,))
C1: =INDEX(x,N)
C2: =INDEX(x,IF(B2<B1,N,C1-1))    (copy it down, except C1)

C2 can not be used in an array formula, because is refers to likewise calculated predecessors. If needed, use this:

C1: =INDEX(x,N+1-ROUND(((2*ROW())^0.5-ROUND((2*ROW())^0.5,)+0.5)/((2*ROW())^0.5-(2*(ROW()-1))^0.5),))

This formula also allows unsorted data in A:A. It calculates the positions without help of "neighbours".