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:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
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

Board Regular
Joined
Jan 19, 2009
Messages
163
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.

Well-known Member
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
I see... This is far more complicated. How large the numbers you want to decompose (3 in your example) might be?

J.Ty.
 

cidfidou

Board Regular
Joined
Jan 19, 2009
Messages
163

ADVERTISEMENT

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

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
And how many clients? Is it possible to assign 0 to a client?
 
Last edited:

cidfidou

Board Regular
Joined
Jan 19, 2009
Messages
163

ADVERTISEMENT

Hi Jty

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

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
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

Board Regular
Joined
Jan 19, 2009
Messages
163
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.

Well-known Member
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
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.
 

Forum statistics

Threads
1,136,445
Messages
5,675,900
Members
419,591
Latest member
mersanko

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
Top