Excel calcultaion optimal number of oneups

Does anyone know how can I automatically get number of items on a sheet based on max number allowed and according to quantity.
For example:
- Max number is 30 and total sum of each "product" must be 30.
- I have an order lets say:
2000 A1
4000 A2
4000 A3
2000 A4

The result I need to be displayed is:
A1 5 pieces
A2 10 pieces
A3 10 pieces
A4 5 pieces

This totals 30 and now I know that I need 400 sheets in total, where I will have each of the items like in the results. Of course the number 30 will change so will the ordered quantity. IS there a formula that can display these results according to those 2 given information?

Hi

You can use

Formula:

Please Login or Register  to view this content.

where C5:C8 are the number of items, D3 are the max allowed.

See the file to clarify