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)

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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
I see... This is far more complicated. How large the numbers you want to decompose (3 in your example) might be?

J.Ty.
 
Upvote 0
And how many clients? Is it possible to assign 0 to a client?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
cidfidou said:
Sry for making you waste your time as I obviously did not need all that and use a wrong example.
Click to expand...

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

cidfidou said:
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.
Click to expand...

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

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

Similar threads

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

Forum statistics

Threads
1,214,819
Messages
6,121,746
Members
449,050
Latest member
excelknuckles

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