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

[cidfidou]

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

 

[J.Ty.]

Hi,

You can download my spreadsheet https://www.mimuw.edu.pl/~jty/SIGMOD/Relational AlgebraOperators.xlsx, which provides Excel implementations of all relational algebra operators, including Cartesian product (worksheet "product"). It is done for two tables of two columns each, while you need only one column, but this can be easily modified (I can help you if you want).

J.Ty.

 

[cidfidou]

Hi J TY,

Thank you so much for your spreadsheet but I dont think this will give the solution I am looking for. If i have understood your spreadsheet, the product tab will give me all the combinations but on top of the combination I am looking at another dimension which is the combination breakdown of the quantity entered in B1. So, from my example, if i entered 3 in B1, I would like to have all the combination of 3, 1 and 2 and 1,1,1 for every Region and client.

 

[J.Ty.]

I see... This is far more complicated. How large the numbers you want to decompose (3 in your example) might be?

J.Ty.

 

[cidfidou]

If I could do until 10 that would be already really good .

 

[J.Ty.]

And how many clients? Is it possible to assign 0 to a client?

 

[cidfidou]

Hi Jty

The minimum would be 6 Regions and 7 clients and it is possible to assign 0.
Thanks

 

[J.Ty.]

Mathematics tells us that, assuming that you want to distribute 3 units, have 6 regions and 7 clients, the total number of possible different ways of doing this is 13244. With 4 units it is 148953, with 5 exceeds 850668, and with your maximal 10 is more than 1471442973, 1400 times more than the maximal number of rows in an Excel worksheet.

I'm afraid it is undoable.

Let's start over: what do you need those assignments for? I guess nobody will ever inspect them one by one, so you probably want to choose ones which are optimal in certain sense. It might be possible without generating them all.

J.Ty.

 

[cidfidou]

Sry for making you waste your time as I obviously did not need all that and use a wrong example.

After more thoughts, I think what I need is to be able to use the randombetween () function over several cells linked to a total MAX. And to generate numbers every time I refresh with F9.

 

[J.Ty.]

Sry for making you waste your time as I obviously did not need all that and use a wrong example.

I do not think my time was wasted. I wanted to help you, no matter how.

After more thoughts, I think what I need is to be able to use the randombetween () function over several cells linked to a total MAX. And to generate numbers every time I refresh with F9.

Does it mean that you want to generate a random assignment satisfying your criteria, rather than all of them?

J.Ty.