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

I

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.


:crash:
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>
[TD="class: xl63, width: 101, align: left"]Customer list
[/TD]
[TD="class: xl64, width: 101, align: left"]Adress[/TD]

[TD="class: xl65, align: left"]STC
[/TD]
[TD="class: xl66, align: left"]Saudi[/TD]

[TD="class: xl65, align: left"]Etisalat
[/TD]
[TD="class: xl66, align: left"]UAE[/TD]

[TD="class: xl65, align: left"]Vodafone
[/TD]
[TD="class: xl66, align: left"]Egypt
[/TD]

[TD="class: xl65, align: left"]MTC
[/TD]
[TD="class: xl66, align: left"]Kuwait[/TD]

[TD="class: xl65, align: left"]Orange
[/TD]
[TD="class: xl66, align: left"]France
[/TD]

</tbody>


[TABLE="width: 322"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Product list[/TD]
[TD]Available Stock (units)[/TD]
[TD]Price per unit[/TD]
[/TR]
[TR]
[TD="align: left"]Router1[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD="align: left"]Router2[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: left"]Router3[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD="align: left"]Router4[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1500[/TD]
[/TR]
[TR]
[TD="align: left"]Router5[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]900[/TD]
[/TR]
[TR]
[TD="align: left"]Router6[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]950[/TD]
[/TR]
[TR]
[TD="align: left"]Router7[/TD]
[TD="align: right"]2400[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: left"]Router8[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]550[/TD]
[/TR]
[TR]
[TD="align: left"]Router9[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]1100[/TD]
[/TR]
[TR]
[TD="align: left"]Router10[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]250[/TD]
[/TR]
</tbody>[/TABLE]


what i want to fill below table

[TABLE="width: 1737"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Invoice Number [/TD]
[TD="align: left"]Customer Name[/TD]
[TD="align: left"]adress[/TD]
[TD="align: left"]DATE[/TD]
[TD="align: left"]Product 1[/TD]
[TD="align: left"]Quantity product 1[/TD]
[TD="align: left"]Price product 1[/TD]
[TD="align: left"]Total Price Product 1[/TD]
[TD="align: left"]Product 2[/TD]
[TD="align: left"]Quantity product 2[/TD]
[TD="align: left"]Price product 2[/TD]
[TD="align: left"]Total Price Product 2[/TD]
[TD="align: left"]Total price without taxes[/TD]
[TD="align: left"]VAT[/TD]
[TD="align: left"]Total TTC[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]Vodafone[/TD]
[TD="align: left"]Egypt[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: left"]Router1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: left"]Router7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]2400[/TD]
[TD="align: right"]3400[/TD]
[TD="align: right"]680[/TD]
[TD="align: right"]4080[/TD]
[/TR]
[TR]
[TD="align: right"]4001[/TD]
[TD="align: left"]Etisalat[/TD]
[TD="align: left"]UAE[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: left"]Router9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]3600[/TD]
[TD="align: left"]Router3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]5100[/TD]
[TD="align: right"]1020[/TD]
[TD="align: right"]6120[/TD]
[/TR]
[TR]
[TD="align: right"]4002
[/TD]
[TD="align: left"]Orange[/TD]
[TD="align: left"]France[/TD]
[TD="align: right"]1/2/2019[/TD]
[TD="align: left"]Router3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: left"]Router10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1200[/TD]
[/TR]
[TR]
[TD="align: right"]4003[/TD]
[TD="align: left"]STC[/TD]
[TD="align: left"]Saudi[/TD]
[TD="align: right"]1/3/2019[/TD]
[TD="align: left"]Router1[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4004[/TD]
[TD="align: left"]Vodafone[/TD]
[TD="align: left"]Egypt[/TD]
[TD="align: right"]1/4/2019[/TD]
[TD="align: left"]Router5[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4005[/TD]
[TD="align: left"]MTC[/TD]
[TD="align: left"]Kuwait[/TD]
[TD="align: right"]1/5/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4006[/TD]
[TD="align: left"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4007[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4008[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]…[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]…[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4996[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4997[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4998[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
You must log in or register to reply here.

Similar threads

M
  • Question Question
Return Multiple Cells From One Column From A Unique cell in another Column
Replies
7
Views
508
oldbrewer
O
M
  • Question Question
I have an excel file with 4 Column, need to populate a separte excel sheet with Top 10 customer from column a who have top 10 values in colum b
Replies
4
Views
490
makvisions
M
J
  • Question Question
Auto fill group data using info from another worksheet based on multiple cells? - Formula Needed.
Replies
1
Views
1K
istiasztalos
I
R
  • Question Question
Help!
Replies
3
Views
2K
63falcondude
63falcondude

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top