cartesian product with Max constraint / VBA code / Power query Trick

C

cidfidou

Board Regular
Joined
Jan 19, 2009
Messages
163
Hi Excel Masters,

I am trying to list all the combinations of a qty X breakdown within a 2 dimensions tables.

In other words, lets imagine I have 1 unit of a product in stock, what are the combinations of destinations that I could send that product to (Clients x in Region Y)

Example 1:

Inputs : Qty 1 in stock in B1 for for below table starting A3(Clients / Regions)

[TABLE="width: 320"]
<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>[TR]
[TD="width: 64"]Qty[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client1[/TD]
[TD]Client2[/TD]
[TD]Client3[/TD]
[TD]……[/TD]
[/TR]
[TR]
[TD]Region1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Region2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Region3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]….[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Outputs: in Col L 1 and M1

[TABLE="width: 173"]
<colgroup><col style="width:48pt" width="64"> <col style="width:82pt" width="109"> </colgroup><tbody>[TR]
[TD="width: 64"]Qty1[/TD]
[TD="width: 109"]Region1 Client1[/TD]
[/TR]
[TR]
[TD]Qty1[/TD]
[TD]Region1 Client2[/TD]
[/TR]
[TR]
[TD]Qty1[/TD]
[TD]Region1 Client3[/TD]
[/TR]
[TR]
[TD]Qty1[/TD]
[TD]Region2 Client1[/TD]
[/TR]
[TR]
[TD]Qty1[/TD]
[TD]Region2 Client2[/TD]
[/TR]
[TR]
[TD]Qty1[/TD]
[TD]Region2 Client3[/TD]
[/TR]
[TR]
[TD]Qty1[/TD]
[TD]Region3 Client1[/TD]
[/TR]
[TR]
[TD]Qty1[/TD]
[TD]Region3 Client2[/TD]
[/TR]
[TR]
[TD]Qty1[/TD]
[TD]Region3 Client3[/TD]
[/TR]
</tbody>[/TABLE]


Example 2:

IF now we have qty 2 in B1, we have more combinations as 2 could be a combination of 2 or 1 and 1.

Input

[TABLE="width: 320"]
<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>[TR]
[TD="width: 64"]Qty[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client1[/TD]
[TD]Client2[/TD]
[TD]Client3[/TD]
[TD]……[/TD]
[/TR]
[TR]
[TD]Region1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Region2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Region3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]….[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Output in L1

[TABLE="width: 353"]
<colgroup><col style="width:48pt" width="64"> <col style="width:217pt" width="289"> </colgroup><tbody>[TR]
[TD="width: 64"]Qty2[/TD]
[TD="width: 289"]2 Region1 Client1[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]2 Region1 Client2[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]2 Region1 Client3[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]2 Region2 Client1[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]2 Region2 Client2[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]2 Region2 Client3[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]2 Region3 Client1[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]2 Region3 Client2[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]2 Region3 Client3[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 1 Client1 / 1 Region1 Client2[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 1 Client1 / 1 Region1 Client3[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 1 Client1 / 1 Region1 Client2[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 2 Client1 / 1 Region2 Client3[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 2 Client1 / 1 Region2 Client2[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 2 Client1 / 1 Region2 Client3[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 3 Client1 / 1 Region3 Client3[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 3 Client1 / 1 Region3 Client2[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 3 Client1 / 1 Region3 Client3[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 1 Client1 / 1 Region 2 Client 1[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 1 Client1 / 1 Region 3 Client 1[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 1 Client 2 / 1 Region 2 Client 2[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 1 Client2 / 1 Region 3 Client 2[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 1 Client 3 / 1 Region 2 Client 3[/TD]
[/TR]
[TR]
[TD]Qty2[/TD]
[TD]1 Region 1 Client2 / 1 Region 3 Client 3[/TD]
[/TR]
</tbody>[/TABLE]




I would love to be able to use a VBA code in order to list all combinations for a any qty in B1 and more than 3 Regions and Clients.

This is defo one of the most difficult thing I have ever try to do in excel. I hope there is someone out there who can solve that.

Thanks in advance
 
Last edited:
Sort by date Sort by votes
Yes exactly. So do you think it would be possible for example to use VBA or a formula over 2 or 3 or 4 ... cells and to have excel randomly allocate quantity in those cells matching the qty of another cell being the constraint.

Input
A1 = 4

Output

in A3 to A5, generating a random combination matching the total 4 (so could 1,1,2 / 2,2,0 / 4,0,0...) and to be able to change the combination with F9 or vBA


Thanks again for your help which is greatly appreciated
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
That is doable. Please give me some time to prepare it for you.

J.Ty.
 
Upvote 0
Hi,

Here is my solution for you. First of all, you can downolad it from my OneDrive https://1drv.ms/x/s!Ajsc9tBqMM65n1-OvPg9mpexZQXe.
Next, the screenshot is below. The construction is rather complex, to assuer that are assignements satisfying your requirements are equiprobable. You can add more Regions/Clients, the spreadsheet will adapt to it automatically.

Please test it.

J.Ty.



Excel 2016 (Windows) 64 bit
ABCDEFGHIJKLMN
1Total stockcomposition seed0random compositionnumber of partstotal region/clientseedactualhelprandom sorted combinationRANDOM ASSIGNMENT
270000115534217171175RegionClientAssignment
36127282281Region 1Client 1 
47134363361Region 1Client 2
5724251#N/ARegion 1Client 3
6712120#N/ARegion 1Client 4
7735393#N/ARegion 1Client 5
8726292#N/ARegion 1Client 6
97660#N/ARegion 2Client 1
107780#N/ARegion 2Client 2
117220#N/ARegion 2Client 3
12Region 2Client 4
13Region 2Client 5
14Region 2Client 6
15Region 3Client 1
16Region 3Client 2
17Region 3Client 3
18Region 3Client 4
19Region 3Client 55
20Region 3Client 6
21Region 4Client 1
22Region 4Client 2
23Region 4Client 3
24Region 4Client 4
25Region 4Client 5
26Region 4Client 6
27Region 5Client 1
28Region 5Client 2
29Region 5Client 3
30Region 5Client 41
31Region 5Client 5
32Region 5Client 6
33Region 6Client 1
34Region 6Client 2
35Region 6Client 3
36Region 6Client 4
37Region 6Client 5
38Region 6Client 61
39Region 7Client 1
40Region 7Client 2
41Region 7Client 3
42Region 7Client 4
43Region 7Client 5
44Region 7Client 6
cidfidou
Cell Formulas
RangeFormula
B2=DEC2BIN(RANDBETWEEN(0,2^(A2-1)-1),A2-1)
C2=IFERROR(SEARCH("1",$B$2,1+C1),$A$2)
C3=IFERROR(SEARCH("1",$B$2,1+C2),$A$2)
D2=IF(C2-B3=0,"",C2-B3)
D3=IF(C3-C2=0,"",C3-C2)
E2=COUNT(D2:D11)
F2=COUNTA($L$3:$L$102)
G2=RANDBETWEEN(1,1+$F$2-ROWS($F$2:$F2))
H2=G2
I2=COUNTIF(OFFSET($H$1,1,0,MIN(ROWS(G$2:G2),$E$2)),H2)+COUNTIF(OFFSET($H$1,1,0,$E$2),"<"&H2)
J2=INDEX($H$2:$H$11,MATCH(ROWS($H$2:$H2),$I$2:$I$11,0))
K2=INDEX($D$2:$D$11,ROWS($H$2:$H2))
N3=IFERROR(INDEX($K$2:$K$11,MATCH(ROWS($L$3:$L3),$J$2:$J$11,0)),"")
 
Upvote 0
Hi Jty

Thank you so much for your amazing solution and for the time you have spent to help a stranger. I really appreciate it. It is working like a charm!!! I wish I could offer you a drink but i am not living in warsaw :-)
 
Upvote 0
That's simple: offer your drink to somebody who lives next to you.

Good luck!

J.Ty.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
K
  • Question Question
VBA Help
2
Replies
13
Views
1K
Logit
Logit
C
  • Question Question
Find Specific Instance of Characters within a String and Return "This" Result
Replies
3
Views
755
jtakw
jtakw
M
  • Question Question
Modified code not working, as expected
2
Replies
15
Views
2K
Mr2017
M
A
  • Question Question
Finding multiple results
Replies
6
Views
817
mohadin
M

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

Share this page

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