Need to Randomly fill columns from other columns till certain criteria is met (stock finish)

is2_egypt

New Member
Joined
Mar 25, 2015
Messages
10
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.


:oops:
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 listAvailable Stock (units)Price per unit
Router11200500
Router2700800
Router3800700
Router46001500
Router5800900
Router61000950
Router72400100
Router8300550
Router98001100
Router10250250

<colgroup><col><col><col></colgroup><tbody>
</tbody>


what i want to fill below table

Invoice Number Customer NameadressDATEProduct 1Quantity product 1Price product 1Total Price Product 1Product 2Quantity product 2Price product 2Total Price Product 2Total price without taxesVATTotal TTC
VodafoneEgypt1/1/2019Router125001000Router74600240034006804080
4001EtisalatUAE1/1/2019Router966003600Router305001500510010206120
4002
OrangeFrance1/2/2019Router301000Router102250100010002001200
4003STCSaudi1/3/2019Router11
4004VodafoneEgypt1/4/2019Router52
4005MTCKuwait1/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>
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Watch MrExcel Video

Forum statistics

Threads
1,114,652
Messages
5,549,210
Members
410,905
Latest member
Extjel
Top