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, 2023 in Others by Kithuzzz
• 38,010 points
749 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, 2023 by narikkadan
• 63,720 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,720 points
582 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,720 points
351 views
0 votes
1 answer

Excel VBA search based on cell values into folders and sub-folders to get the file path and data

This will create a listing of all ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,720 points
1,807 views
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, 2023 in Others by narikkadan
• 63,720 points
455 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,720 points
455 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
373 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, 2023 in Others by narikkadan
• 63,720 points
276 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, 2023 in Others by narikkadan
• 63,720 points
2,233 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, 2023 in Others by narikkadan
• 63,720 points
861 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,720 points
1,169 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