Macro + Excel functions: Braking a cell value in random number of parts to keep total same;each part mentioned in different cell;

[darpandce]

Dear Excel Experts,

I have problem:

I have a column (say, A) having 1000 values; ranging from anything 2 to 35 in each of the cell of that column.

suppose, cell A1 has value of 20. I need to divide this value of 20 in some 'random' number of parts.. e.g., mentioning randbetween (1,5)..so that sum of all the parts should equal to 20 only.. and each part should be integer. again, i have no fixed percentage that each part would be having.

Also, each part must be put into a separate column .i.e., if 20 has been broken into 4 parts of 6,9,4,1 .. then these numbers will be put in different column say.. D1,D2,D3,D4...

once i do it for single cel... then I need to run this for all 1000 cells mentioned in column 'A'... with each split entry getting appended after the last division of entries.... eg.. '20' was divided into D1,D2,D3,D4.....so, next cell value, once divided, will continue from D5, D6, D7.. etc..


Please help urgently

 

[AD_Taylor]

Is there a minimum/maximum number of parts you would want?

E.g.

For the number 2 is 1 part ok and for 2 to be transferred over or would you like it to always be 1, 1?
Similarly for 20 would 20 parts be ok 1,1,1,etc?