Excel Get different permutation combination of the values

0 votes

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)

enter image description here

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

enter image description here

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?

Feb 13 in Others by Kithuzzz
• 38,010 points
479 views

1 answer to this question.

0 votes

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.

enter image description here

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:

enter image description here

answered Feb 13 by narikkadan
• 63,180 points

Related Questions In Others

0 votes
1 answer

Get random value in the range of plus/minus 10% of a cell value in Excel

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,180 points
336 views
0 votes
1 answer

Excel-How can I get the address of a cell instead of a value?

There are various difficulties in this. Which ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 63,180 points
191 views
0 votes
1 answer
0 votes
1 answer

Sum the total of a column in excel and paste the sum to a different workbook

Get Column Sum From Closed Workbook Option Explicit Sub ...READ MORE

answered Jan 26 in Others by narikkadan
• 63,180 points
268 views
0 votes
1 answer

Can a worksheet ActiveX ComboBox work on a Mac?

ActiveX is an outdated Windows technology that ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,180 points
342 views
0 votes
0 answers

Strikethrough in Excel VBA

When I enter the second date in ...READ MORE

Nov 27, 2022 in Others by Kithuzzz
• 38,010 points
271 views
0 votes
1 answer

Excel VBA- Creation of a New datablock with criteria

To insert the dropdown, you can go ...READ MORE

answered Jan 12 in Others by narikkadan
• 63,180 points
171 views
0 votes
1 answer

I want to compare two Excel files and highlight the differences with VBA

The workbook doesn't have the UsedRange property ...READ MORE

answered Jan 13 in Others by narikkadan
• 63,180 points
1,525 views
0 votes
1 answer

How to programmatically get the values of a spilled Excel range in VBA?

By using the Text property, I was ...READ MORE

answered Mar 23 in Others by narikkadan
• 63,180 points
413 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,180 points
886 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP