Hello Experts,i have a little challenging question but not challenging for you for sure , i have some customers data and stock and prices , i need to fill in a table for invoices and distribute the stock randomly between the invoices so that each invoice value does not exceed 5000, same customer can have more than one invoice , each customer can have from 1 to 5 items in his invoice , appreciate your help , all details in attached sheet with colors to make it easier , above requirements are mentioned in the sheet as well in a clearer way.
Requirements :
Fill the following columns randomly from first tab database (customer name/address , Product1 , poduct2 , and quantity columns in Grey randomly as well )
not mandatory to fill the 2 products columns , it can be empty as well so we have one product only.
each row can have from 1 to max 2 products in the invoice
the formula shall distribute the stock between invoices with condition that each invoice does not exceed 5000
i wish i get this in Excel formula
<colgroup><col style="mso-width-source:userset;mso-width-alt:3225; width:76pt" width="101" span="2"> </colgroup><tbody>
</tbody>
<colgroup><col><col><col></colgroup><tbody>
</tbody>
what i want to fill below table
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Requirements :
Fill the following columns randomly from first tab database (customer name/address , Product1 , poduct2 , and quantity columns in Grey randomly as well )
not mandatory to fill the 2 products columns , it can be empty as well so we have one product only.
each row can have from 1 to max 2 products in the invoice
the formula shall distribute the stock between invoices with condition that each invoice does not exceed 5000
i wish i get this in Excel formula
| Customer list | Adress |
| STC | Saudi |
| Etisalat | UAE |
| Vodafone | Egypt |
| MTC | Kuwait |
| Orange | France |
<colgroup><col style="mso-width-source:userset;mso-width-alt:3225; width:76pt" width="101" span="2"> </colgroup><tbody>
</tbody>
| Product list | Available Stock (units) | Price per unit |
| Router1 | 1200 | 500 |
| Router2 | 700 | 800 |
| Router3 | 800 | 700 |
| Router4 | 600 | 1500 |
| Router5 | 800 | 900 |
| Router6 | 1000 | 950 |
| Router7 | 2400 | 100 |
| Router8 | 300 | 550 |
| Router9 | 800 | 1100 |
| Router10 | 250 | 250 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
what i want to fill below table
| Invoice Number | Customer Name | adress | DATE | Product 1 | Quantity product 1 | Price product 1 | Total Price Product 1 | Product 2 | Quantity product 2 | Price product 2 | Total Price Product 2 | Total price without taxes | VAT | Total TTC |
| Vodafone | Egypt | 1/1/2019 | Router1 | 2 | 500 | 1000 | Router7 | 4 | 600 | 2400 | 3400 | 680 | 4080 | |
| 4001 | Etisalat | UAE | 1/1/2019 | Router9 | 6 | 600 | 3600 | Router3 | 0 | 500 | 1500 | 5100 | 1020 | 6120 |
| 4002 | Orange | France | 1/2/2019 | Router3 | 0 | 100 | 0 | Router10 | 2 | 250 | 1000 | 1000 | 200 | 1200 |
| 4003 | STC | Saudi | 1/3/2019 | Router1 | 1 | |||||||||
| 4004 | Vodafone | Egypt | 1/4/2019 | Router5 | 2 | |||||||||
| 4005 | MTC | Kuwait | 1/5/2019 | |||||||||||
| 4006 | ||||||||||||||
| 4007 | ||||||||||||||
| 4008 | ||||||||||||||
| … | ||||||||||||||
| … | ||||||||||||||
| 4996 | ||||||||||||||
| 4997 | ||||||||||||||
| 4998 |
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
