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


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)

Qty 1
Client1 Client2 Client3 ……
Region1
Region2
Region3
….
Outputs: in Col L 1 and M1
Qty1 Region1 Client1
Qty1 Region1 Client2
Qty1 Region1 Client3
Qty1 Region2 Client1
Qty1 Region2 Client2
Qty1 Region2 Client3
Qty1 Region3 Client1
Qty1 Region3 Client2
Qty1 Region3 Client3
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
Qty 2
Client1 Client2 Client3 ……
Region1
Region2
Region3
….
Output in L1
Qty2 2 Region1 Client1
Qty2 2 Region1 Client2
Qty2 2 Region1 Client3
Qty2 2 Region2 Client1
Qty2 2 Region2 Client2
Qty2 2 Region2 Client3
Qty2 2 Region3 Client1
Qty2 2 Region3 Client2
Qty2 2 Region3 Client3
Qty2 1 Region 1 Client1 / 1 Region1 Client2
Qty2 1 Region 1 Client1 / 1 Region1 Client3
Qty2 1 Region 1 Client1 / 1 Region1 Client2
Qty2 1 Region 2 Client1 / 1 Region2 Client3
Qty2 1 Region 2 Client1 / 1 Region2 Client2
Qty2 1 Region 2 Client1 / 1 Region2 Client3
Qty2 1 Region 3 Client1 / 1 Region3 Client3
Qty2 1 Region 3 Client1 / 1 Region3 Client2
Qty2 1 Region 3 Client1 / 1 Region3 Client3
Qty2 1 Region 1 Client1 / 1 Region 2 Client 1
Qty2 1 Region 1 Client1 / 1 Region 3 Client 1
Qty2 1 Region 1 Client 2 / 1 Region 2 Client 2
Qty2 1 Region 1 Client2 / 1 Region 3 Client 2
Qty2 1 Region 1 Client 3 / 1 Region 2 Client 3
Qty2 1 Region 1 Client2 / 1 Region 3 Client 3
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


This question generated 10 answers. To proceed to the answers, click here.

This thread is current as of April 29, 2017.


For more resources for Microsoft Excel