combinations generater?

excelNewbie22

Well-known Member
Joined
Aug 4, 2021
Messages
510
Office Version
  1. 365
Platform
  1. Windows
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)
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Not sure if I've understood correctly, but is this what you want. I have only shown the first few rows of the output
Fluff.xlsm
ABCD
11235
2
31111
41112
51113
61115
71121
81122
91123
101125
111131
121132
131133
141135
151151
161152
171153
181155
191211
201212
211213
221215
231221
241222
251223
261225
271231
281232
291233
301235
Original
Cell Formulas
RangeFormula
A3:D258A3=LET(b,MID(BASE(SEQUENCE(4^4,,0),4,4),SEQUENCE(,4),1)+1,INDEX(A1:D1,b))
Dynamic array formulas.
 
Upvote 0
thanks, close.... i'll try and clarify:
baseline is range of 4 numbers, could be 1-2-3-4 could be 5-2-8-7 or 5-5-3-8 and etc
i want all possible combo's of 4 numbers from range of 8 numbers
while the range is 1-2-3-4-5-6-7-8
but with two numbers from the baseline=1234 or 5287 and so on...
example:
1-8-8-2
3-
5-4-6
1-4-7-5
but if the baseline is four different numbers (like 1234) then no duplicates numbers from it allowed in all possible combo's (like 1-1-5-6)
but if the baseline is four numbers with duplicates like 3-3-5-6 then only the duplicate ones also allowed to repeated in all possible combo's like 3-1-2-3 but no 2-6-2-1
 
Upvote 0
I'm afraid I don't understand what you are saying.
Can you please post an actual example of what you are after.
 
Upvote 0
hope this will do it:

test.xlsx
ABCDEFGHIJK
1baseline example 1:
21243
3
4all combinations from range of 1-8 and must include atleast 2 numbers from baseline 1:
51245
68246numbers in red from baseline 1
72134
83456
97531
101865no good because there's only 1 number from baseline 1
115667no good because there's no numbers from baseline 1
12
13
14
15or with duplicates:baseline example 2:
165751
17
18all combinations from range of 1-8 and must include atleast 2 numbers from baseline 2:
195586
207712because there 7 and 1 from baseline 2 the other 7 don't rull it out
215216numbers in red from baseline 2
228455
237615
247732no good because there's two 7's which not in baseline 2
251286no good cause there's no 2 numbers from baseline 2 only 1
test
 
Upvote 0
Ok, I think I understand what you want now, but unfortunately I don't know how to do that.
Hopefully someone else will step in.
 
Upvote 0
First few rows of 2816 rows

Book3
ABCDEFGHIJ
112341111
21112
31113
41114
51115
61116
71117
81118
91121
101122
111123
121124
131125
141126
151127
161128
171131
181132
191133
201134
211135
Sheet1
Cell Formulas
RangeFormula
A1:D1A1=SEQUENCE(1,4)
F1:I2816F1=LET(m, MOD(INT((SEQUENCE(8^4)-0.5)/8^SEQUENCE(1,4,3,-1)),8)+1, FILTER(m,BYROW(m, LAMBDA(x, SUM(--(x=TRANSPOSE(A1#)))>1))))
Dynamic array formulas.


or in power query:

Power Query:
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.Count(List.RemoveMatchingItems(Record.ToList(_), lst0)) < 3 )
in
    Result
 
Upvote 0
Actually, we don't need the transpose so this shorter version also works (a little faster):

Excel Formula:
=LET(m, MOD(INT((SEQUENCE(8^4)-0.5)/8^SEQUENCE(1,4,3,-1)),8)+1, FILTER(m,BYROW(m, LAMBDA(x, SUM(--(x=A1#))>1))))
 
Upvote 0
JGordon11
tried it for checking the rest of it, but it returns #NAME?
but
still it doesn't seem right cause for example in f1:i1 there's 1-1-1-1
and i need it to return only 2 unique numbers from a1:d1 and the 1's above are duplicates which not in in a1:d1
(but if there's was duplicates in a1:d1 like 1-1-3-4 then it'll be ok)
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top