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)
<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
</tbody>
Outputs: in Col L 1 and M1
<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
<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
</tbody>
Output in L1
<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
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 | ||||
…. |
<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
</tbody>
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 |
<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
Qty | 2 | |||
Client1 | Client2 | Client3 | …… | |
Region1 | ||||
Region2 | ||||
Region3 | ||||
…. |
<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
</tbody>
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 |
<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: