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

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)

Qty1
Client1Client2Client3……
Region1
Region2
Region3
….

<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
</tbody>


Outputs: in Col L 1 and M1

Qty1Region1 Client1
Qty1Region1 Client2
Qty1Region1 Client3
Qty1Region2 Client1
Qty1Region2 Client2
Qty1Region2 Client3
Qty1Region3 Client1
Qty1Region3 Client2
Qty1Region3 Client3

<colgroup><col style="width:48pt" width="64"> <col style="width:82pt" width="109"> </colgroup><tbody>
</tbody>


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

Qty2
Client1Client2Client3……
Region1
Region2
Region3
….

<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
</tbody>



Output in L1

Qty22 Region1 Client1
Qty22 Region1 Client2
Qty22 Region1 Client3
Qty22 Region2 Client1
Qty22 Region2 Client2
Qty22 Region2 Client3
Qty22 Region3 Client1
Qty22 Region3 Client2
Qty22 Region3 Client3
Qty21 Region 1 Client1 / 1 Region1 Client2
Qty21 Region 1 Client1 / 1 Region1 Client3
Qty21 Region 1 Client1 / 1 Region1 Client2
Qty21 Region 2 Client1 / 1 Region2 Client3
Qty21 Region 2 Client1 / 1 Region2 Client2
Qty21 Region 2 Client1 / 1 Region2 Client3
Qty21 Region 3 Client1 / 1 Region3 Client3
Qty21 Region 3 Client1 / 1 Region3 Client2
Qty21 Region 3 Client1 / 1 Region3 Client3
Qty21 Region 1 Client1 / 1 Region 2 Client 1
Qty21 Region 1 Client1 / 1 Region 3 Client 1
Qty21 Region 1 Client 2 / 1 Region 2 Client 2
Qty21 Region 1 Client2 / 1 Region 3 Client 2
Qty21 Region 1 Client 3 / 1 Region 2 Client 3
Qty21 Region 1 Client2 / 1 Region 3 Client 3

<colgroup><col style="width:48pt" width="64"> <col style="width:217pt" width="289"> </colgroup><tbody>
</tbody>




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:
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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