combinations generater?

[excelNewbie22]

hi!
i have 4 numbers in 4 cells:
a1=1 b1=2 c1=3 d1=4
(could be also 1-2-3-3)
how to generate all possible combinations of 4 numbers
out of 8 (1-2-3-4-5-6-7-8)
with repeats
and two out of the 4's (can be3's too)
need to be from the original range (a1:d1),
like 2-1-7-7 or 4,4,8,6 ?

very favorable by formula/s if possible

p.s
order not important (like 2-1-3-4 and also 3-2-1-4)

 

[Fluff]

@JGordon11 AFAIK xl2021 doesn't have the Lambda function.

 

[JGordon11]

Sounds like your version of excel doesn't have one or more of the functions I used. So power query or vba may be your best rout.

for match list of 1-2-3-4 is the following correct?

1-1-1-1 no good
1-2-1-2 keep
1-1-1-3 keep
8-1-1-3 keep
1-1-1-8 no good

And the 1-2-3-4 list is not static, meaning you might change it w-x-y-z where w,x,y,z are integers in [1,8]?

 

[excelNewbie22]

correct to all
can i add the lambda function manually?

 

[Fluff]

can i add the lambda function manually?

Nope, pretty sure it's only available with xl 365

 

[excelNewbie22]

Nope, pretty sure it's only available with xl 365

bummer, but still thanks to u all!

 

[JGordon11]

Without lambda and byrows functions I don't know how to spill the results from a single cell formula. But here's an approach using helper columns:

columns F:I spill from a single formula

column J needs to be copied down (to J4096)

Result is in columns L:O spilling from a single formula

First few rows depicted below

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1	2	3	4		1	1	1	1	FALSE		1	1	1	2
2						1	1	1	2	TRUE		1	1	1	3
3						1	1	1	3	TRUE		1	1	1	4
4						1	1	1	4	TRUE		1	1	2	1
5						1	1	1	5	FALSE		1	1	2	2
6						1	1	1	6	FALSE		1	1	2	3
7						1	1	1	7	FALSE		1	1	2	4
8						1	1	1	8	FALSE		1	1	2	5
9						1	1	2	1	TRUE		1	1	2	6
10						1	1	2	2	TRUE		1	1	2	7
11						1	1	2	3	TRUE		1	1	2	8
12						1	1	2	4	TRUE		1	1	3	1
13						1	1	2	5	TRUE		1	1	3	2
14						1	1	2	6	TRUE		1	1	3	3
15						1	1	2	7	TRUE		1	1	3	4
16						1	1	2	8	TRUE		1	1	3	5
17						1	1	3	1	TRUE		1	1	3	6
18						1	1	3	2	TRUE		1	1	3	7
19						1	1	3	3	TRUE		1	1	3	8
20						1	1	3	4	TRUE		1	1	4	1
21						1	1	3	5	TRUE		1	1	4	2
Sheet1 (2)

Cell Formulas
Range	Formula
A1:D1	A1	=SEQUENCE(1,4)
F1:I4096	F1	= MOD(INT((SEQUENCE(8^4)-0.5)/8^SEQUENCE(1,4,3,-1)),8)+1
L1:O2364	L1	=FILTER(F1#,J1:J4096)
J1:J21	J1	=SUM(--(COUNTIF(F1:I1,A$1#)>0))>1
Dynamic array formulas.

if you want to use power query here is revised PQ M code:

let
    lst0 = Record.ToList(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]{0}),
    lst = {1..8},
    tbl = Table.FromColumns({{lst}},{"Column1"}),
    tbl1 = List.Accumulate({2..4}, tbl, (s,c)=> Table.AddColumn(s, "Column" & Text.From(c), each lst)),
    tbl2 = List.Accumulate({1..4}, tbl1, (s,c)=> Table.ExpandListColumn(s, "Column" & Text.From(c))),
    Result = Table.SelectRows(tbl2, each  List.Accumulate(lst0, {Record.ToList(_),0}, (s,c) => let p = List.PositionOf(s{0},c) in if p>=0 then {List.RemoveRange(s{0},p,1), s{1}+1} else s){1} >1)
in
    Result

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	1	2	3	5
2
3	1	1	1	2
4	1	1	1	3
5	1	1	1	5
6	1	1	2	1
7	1	1	2	2
8	1	1	2	3
9	1	1	2	4
10	1	1	2	5
11	1	1	2	6
12	1	1	2	7
13	1	1	2	8
14	1	1	3	1
15	1	1	3	2
16	1	1	3	3
17	1	1	3	4
18	1	1	3	5
19	1	1	3	6
20	1	1	3	7
21	1	1	3	8
22	1	1	4	2
23	1	1	4	3
24	1	1	4	5
25	1	1	5	1
26	1	1	5	2
27	1	1	5	3
28	1	1	5	4
29	1	1	5	5
30	1	1	5	6
31	1	1	5	7
32	1	1	5	8
33	1	1	6	2
34	1	1	6	3
35	1	1	6	5
36	1	1	7	2
37	1	1	7	3
38	1	1	7	5
39	1	1	8	2
40	1	1	8	3
Data

Cell Formulas
Range	Formula
A3:D2366	A3	=LET(b,BASE(SEQUENCE(8^4,,0),8,4)+"1111",m,--MID(b,{1,2,3,4},1),FILTER(m,(ISNUMBER(FIND(A1,b)))+(ISNUMBER(FIND(B1,b)))+(ISNUMBER(FIND(C1,b)))+(ISNUMBER(FIND(D1,b)))>1))
Dynamic array formulas.

 

[JGordon11]

How about

Fluff.xlsm
	A	B	C	D
1	1	2	3	5
2
3	1	1	1	2
4	1	1	1	3
5	1	1	1	5
6	1	1	2	1
7	1	1	2	2
8	1	1	2	3
9	1	1	2	4
10	1	1	2	5
11	1	1	2	6
12	1	1	2	7
13	1	1	2	8
14	1	1	3	1
15	1	1	3	2
16	1	1	3	3
17	1	1	3	4
18	1	1	3	5
19	1	1	3	6
20	1	1	3	7
21	1	1	3	8
22	1	1	4	2
23	1	1	4	3
24	1	1	4	5
25	1	1	5	1
26	1	1	5	2
27	1	1	5	3
28	1	1	5	4
29	1	1	5	5
30	1	1	5	6
31	1	1	5	7
32	1	1	5	8
33	1	1	6	2
34	1	1	6	3
35	1	1	6	5
36	1	1	7	2
37	1	1	7	3
38	1	1	7	5
39	1	1	8	2
40	1	1	8	3
Data

Cell Formulas
Range	Formula
A3:D2366	A3	=LET(b,BASE(SEQUENCE(8^4,,0),8,4)+"1111",m,--MID(b,{1,2,3,4},1),FILTER(m,(ISNUMBER(FIND(A1,b)))+(ISNUMBER(FIND(B1,b)))+(ISNUMBER(FIND(C1,b)))+(ISNUMBER(FIND(D1,b)))>1))
Dynamic array formulas.

Like the base 8 approach to generating full combinations list!

The filter part works if the a1:d1 are distinct, but if any are repeated it will include rows it shouldn't.

For example if 1-1-2-3 are the match criteria then 1-4-5-6 is included (among others) where it only has one of the matching elements not at least two.

 

[Fluff]

if any are repeated it will include rows it shouldn't.

You're quite right, obviously didn't test for that.
Hey-ho back to the drawing board

 

[Fluff]

If you're interested in a macro, how about

Sub excelNewbie()
   Dim Ary As Variant, Nary As Variant
   Dim c As Long, r As Long, nr As Long, x As Long
   Dim Tmp As String
   
   Ary = [Base(Sequence(8 ^ 4, , 0), 8, 4) + "1111"]
   ReDim Nary(1 To UBound(Ary), 1 To 4)
   For r = 1 To UBound(Ary)
      Tmp = Ary(r, 1)
      For c = 1 To 4
         x = InStr(1, Tmp, Cells(1, c))
         If x > 0 Then Tmp = Application.Replace(Tmp, x, 1, "")
      Next c
      If Len(Tmp) < 3 Then
         nr = nr + 1
         For c = 1 To 4
            Nary(nr, c) = Mid(Ary(r, 1), c, 1) + 0
         Next c
      End If
   Next r
   Range("A3").Resize(nr, 4).Value = Nary
End Sub