Do you have BYROW, e.g. can you do this?
When I looked up information about Excel 2021 yesterday, I noticed it has all of the functions you used. (I didn't think to look at your account details to see that you have Office 2021 before I did so.) So I figured your formula would work for him.
Maybe he isn't aware that
the formula cannot take more than 20 numbers, as 2^20 = Rows.Count. So perhaps he tried to have more than 20 numbers, because in
this post, he said he wanted to have up to 35 numbers. In theory, he
should be able to compute up to (any number of numbers taken at a time for) 22 numbers (because 22 choose 11 is 705,432, but 23 choose 12 is 1,352,078 > Rows.Count), but the original formula uses 2^n, so the actual limit is 20 numbers.
So if he wants to have up to (any number taken at a time) of 22 numbers, he needs to use the VBA code posted in
his other thread. But if he wants to have up to 35 numbers (taken any at a time), then he needs to ask them to modify their VBA code to have them export the combinations to .txt files (which I explained the necessity of in
this post).
Also, I did this modification to the original formula after my previous post yesterday, which I know
should work for him (as long as he has up to 2 digit numbers, and up to 12 numbers (in columns
A -
L). It will work taking any number taken at a time.). The weird thing about it is, you need to have A1:M1 if your set of numbers spans A1:L1, etc. (The range in the formula needs to be 1 extra column than the actual alotted range for number input.) Therefore A1:M1 is in the formula, but the formula only actually compute A1:L1.
Excel Formula:
=LET(r,6,rng,A1:M1,y,COUNTA(rng),TRANSPOSE(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("?",,IFNA(IF(FILTER(MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(,y+COLUMNS(rng)-y-1,0)),2),MMULT(MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(,y,0)),2),SEQUENCE(y)^0)=r),rng,"-"),"|")),"-?",""),"?|?","|"),"?","-"),"-|",""),"|")))
But just in case all else fails, here's a minor modification of the original formula, a new formula, and some VBA code to fill down the new formula.
Notes:
- The 2 formulas must go in the exact cells that they are in.
- You need to type an X in between the two cells that you placed the 2 formulas (as shown).
- I "wrapped" the number cells to 10 columns . . . the numbers are now on 2 rows instead of 1. The 20 blue cells represents the 20 numbers "allowed" for this formula to work. (Based on the 20 number limit mentioned above.) I did this because it will enable you to not have to scroll horizontally. (Numbers are to be entered from left to right, top to bottom.)
- You must change the number taken at a time in Cell A3.
- After you have changed either the quantity of numbers and/or the value in Cell A3, double click on Cell A3 to fill down the new formula.
- This VBA code also resets the used range. (A feature not had before with the formulas alone.) This way the scroll range ends where the content does.
- If you perhaps change the range with numbers from A1:J2 to something else, you will need to change it both at the beginning of the (modified) original formula and at the beginning of Sub Carry_Down_Formula's code.
- If you perhaps change the range with the number taken at a time from A3 to something else (by cutting and pasting that cell to a different location on the spreadsheet), you need to change it both at the beginning of the of Sub Carry_Down_Formula's code AND in Sub Worksheet_BeforeDoubleClick. (There is no need to change it in the original formula anymore.)
But they don't need to be changed, IMO.
Here's the spreadsheet:
Here's the VBA code to put in a standard code module:
VBA Code:
Sub Carry_Down_Formula()
'Inputs
Dim numberTakenAtATime As String, rangeWhereNumbersAre As String
numberTakenAtATime = "A3"
rangeWhereNumbersAre = "A1:J2"
'Carry down the formula.
Dim columnWhereListIs As Integer, numberOfItems As Integer, lastRowToFillTo As Long
columnWhereListIs = Application.WorksheetFunction.Match("X", Range("1:1"), 0) - 1
Range(Cells(2, columnWhereListIs), Cells(Rows.Count, columnWhereListIs)).ClearContents
numberOfItems = Application.WorksheetFunction.CountA(Range(rangeWhereNumbersAre))
lastRowToFillTo = Application.WorksheetFunction.Combin(numberOfItems, Range(numberTakenAtATime).Value)
Range(Cells(1, columnWhereListIs), Cells(lastRowToFillTo, columnWhereListIs)).Formula2 = Cells(1, columnWhereListIs).Formula2
'Reset the used range.
Dim lastRowToDelete As Long
lastRowToDelete = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
If (lastRowToFillTo > Range("A1").CurrentRegion.Rows.Count) And (lastRowToDelete > lastRowToFillTo + 1) Then Range(lastRowToFillTo + 1 & ":" & ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1).EntireRow.Delete
End Sub
The code to put in the Worksheet's sheet code module:
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = Range("A3").Address Then
Cancel = True
Call Carry_Down_Formula
End If
End Sub
This is after double clicking on Cell
A3, for example:
^0)=6) - for generating combinations of x numbers
