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