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

Task: 

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".