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