Distributing cell value (not text-to-columns)
 

Hi

I have a spreadsheet of weight values that I want to distribute evenly into 500g lots. Let's say I'm importing marbles and I have a whole bunch of varieties that need to be kept separate from each other. See below for the raw data.

DATE Country Origin Marble Lot Weight (kg)
01/04/2013 AUS 2 MAR001 2.6
01/04/2013 AUS 3 MAR001 1.9
01/04/2013 AUS 3 MAR002 0.7
01/04/2013 AUS 3 MAR003 3.7
01/04/2013 AUS 3 MAR004 2.02
01/04/2013 AUS 4 MAR004 0.24

What I would like to do is have a formulated spreadsheet that picks up this data and separates it into 500g lots and assigns a sequential letter of the alphabet to it and also puts out the remainder (the left overs in a smaller bag that is <500). This last part is less essential than the overall function of the distribution to the correct number of 500g segments. The ideal output will look like this:

DATE Country Origin Marble Lot Weight (kg)
01/04/2013 AUS 2 MAR001 A 0.5
01/04/2013 AUS 2 MAR001 B 0.5
01/04/2013 AUS 2 MAR001 C 0.5
01/04/2013 AUS 2 MAR001 D 0.5
01/04/2013 AUS 2 MAR001 E 0.5
01/04/2013 AUS 2 MAR001 F 0.1
01/04/2013 AUS 3 MAR001 A 0.5
01/04/2013 AUS 3 MAR001 B 0.5
01/04/2013 AUS 3 MAR001 C 0.5
01/04/2013 AUS 3 MAR001 D 0.4
01/04/2013 AUS 3 MAR002 A 0.5
01/04/2013 AUS 3 MAR002 B 0.2
01/04/2013 AUS 3 MAR003 A 0.5
01/04/2013 AUS 3 MAR003 B 0.5
01/04/2013 AUS 3 MAR003 C 0.5
01/04/2013 AUS 3 MAR003 D 0.5
01/04/2013 AUS 3 MAR003 E 0.5
01/04/2013 AUS 3 MAR003 F 0.5
01/04/2013 AUS 3 MAR003 G 0.5
01/04/2013 AUS 3 MAR003 H 0.2
01/04/2013 AUS 3 MAR004 A 0.5
01/04/2013 AUS 3 MAR004 B 0.5
01/04/2013 AUS 3 MAR004 C 0.5
01/04/2013 AUS 3 MAR004 D 0.5
01/04/2013 AUS 3 MAR004 E 0.02
01/04/2013 AUS 4 MAR004 A 0.24

Apologies for the large dataset but I need these different combinations to work. Is there anyone out there that knows of a way to do this?