Break down a total quantity into columns

[joshc]

Hello All!

Got a query which I hope someone can help with.. I have a spreadsheet that I need to create for work due to a new process. Essentially what I need help on is a formula which automatically breaks down the allocation (column B) into packs (columns D-M). Below is a shortened version of what we use - could be up to 100 packs per store and over 1000 stores for example.

	Allocation	Pack Size	Pack 1	Pack 2	Pack 3	Pack 4	Pack 5	Pack 6	Pack 7	Pack 8	Pack 9	Pack 10
Store 1	750	75
Store 2	250	75
Store 3	165	75
Store 4	50	75
Store 5	75	75
Store 6	315	75
Store 7	105	75
Store 8	50	75
Store 9	175	75
Store 10	200	75

<tbody>
</tbody>

The maximum pack size is in column C and each pack should not exceed this value. If we look at the first 2 rows of data it would be broken down as per the below.

	Allocation	Pack Size	Pack 1	Pack 2	Pack 3	Pack 4	Pack 5	Pack 6	Pack 7	Pack 8	Pack 9	Pack 10
Store 1	750	75	75	75	75	75	75	75	75	75	75	75
Store 2	250	75	75	75	75	25
Store 3	165	75
Store 4	50	75
Store 5	75	75
Store 6	315	75
Store 7	105	75
Store 8	50	75
Store 9	175	75
Store 10	200	75

<tbody>
</tbody>

Any help appreciated!

Cheers
Josh

 

[Joe4]

Place this formula in D2:
=MIN($C2,$B2)
copy down for all of column D

Place this formula in E2:
=MIN($C2,$B2-SUM($D2:D2))
and copy to all the rest of your cells in columns E through M

 

[joshc]

Perfect, thanks Joe.

 

[Joe4]

You are welcome.