# 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 thread is current as of April 29, 2017.