How to create Excel list with data that excludes certain types of combinations

Question

I have two lists with the same set of data (e.g. A, B, C, D), and I want to be able to create a list of combinations that exclude the same data twice (e.g. A/A, B/B, etc) and reversed sets (i.e. A/B means I don't want B/A)

I'm using Excel for Mac 2011, if that matters.

Edit:

Ideally I would like to use Tables instead of inputting individual data pieces because the actual list is very long

The following tables are named T_TESTA and T_TESTB:

Edit 2:

Both solutions presented to me below are working perfectly, but I'm only allowed to pick one. So I'm picking the one based on streamlining an issue that came up for me that's entirely unrelated to the code itself. The other solution is slightly more elegant for presenting the data together in a list.

Answer 1

Here are a couple of formulas I worked out for Google Sheets here and translated back into Excel:

=IFERROR(INDEX(A:A,CEILING((2*COUNTA(A:A)-1-SQRT((2*COUNTA(A:A)-1)^2-8*ROW()))/2,1)),"")

and

=IFERROR(INDEX(A:A,COUNTA(A:A)+ROW()-((2*COUNTA(A:A)-1)*CEILING(((2*COUNTA(A:A)-1)-SQRT((2*COUNTA(A:A)-1)^2-8*ROW()))/2,1)-CEILING(((2*COUNTA(A:A)-1)-SQRT((2*COUNTA(A:A)-1)^2-8*ROW()))/2,1)^2)/2),"")

Answer 2

Try this short macro:

Sub Kombo()
    arr = Array("A", "B", "C", "D")

    k = 1
    For i = 0 To 2
        For j = i + 1 To 3
            Cells(k, 1) = arr(i) & arr(j)
            k = k + 1
        Next j
    Next i

End Sub

The loops are configured to create combinations rather than permutations.

EDIT#1:

In this version of the code we get the values from cells rather than an internal array. I assume the values are in E1 through E4:

Sub Kombo2()
    Dim arr(0 To 3) As Variant

    For i = 0 To 3
        arr(i) = Range("E" & i + 1).Value
    Next i

    k = 1
    For i = 0 To 2
        For j = i + 1 To 3
            Cells(k, 1) = arr(i) & arr(j)
            k = k + 1
        Next j
    Next i

End Sub

The results are still displayed in column A