Excel Get different permutation combination of the values

Question

I have an excel sheet with two columns, each with ten values, as shown in the diagram below. The 10 values from columns A and B are combined in a drop-down menu in columns E and F. I want a drop-down menu in column D, "Result," to display 100 distinct possible permutations of the data. I attempted to write a macro, but I kept getting lost. EDIT: I've added the error I'm experiencing. I sincerely appreciate any assistance. An illustration of what to expect (remember columns E and F are dropdowns)

Below is the macro I have tried:

Sub Combination()
Dim arr1 As Variant
Dim arr2 As Variant
Dim i As Long, j As Long, k As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
arr1 = ws.Range("E1", ws.Range("E" & ws.Rows.Count).End(xlUp).Row).Value
arr2 = ws.Range("F1", ws.Range("F" & ws.Rows.Count).End(xlUp).Row).Value
ws.Range("D1").Value = "Result"
k = 1
For i = LBound(arr1, 1) To UBound(arr1, 1)
For j = LBound(arr2, 1) To UBound(arr2, 1)
ws.Range("D" & k + 1).Value = arr1(i, 1) & ", " & arr2(j, 1)
k = k + 1
If k = 101 Then Exit For
Next j
If k = 101 Then Exit For
Next i
End Sub

Debugger shows an error in this line of code:

arr1 = ws.Range("E1", ws.Range("E" & ws.Rows.Count).End(xlUp).Row).Value

How else am I supposed to read the values in the drop-down?

Answer 1

This work can be completed utilising dynamic spreadsheet functions rather than necessarily a VBA solution (if you have a relatively recent version of Excel). I believe that people use VBA far too frequently when they should first explore the possibilities of spreadsheet functions.

1. Calculate the permutations

Put this formula in cell H2:

=LET(a,A2:A11,b,B2:B10,na,ROWS(a),nb,ROWS(b),s,SEQUENCE(na*nb,,0),INDEX(a,1+(INT(s/nb))) & "," & INDEX(b,1+MOD(s,nb)))

2. Set the Data Validation: