Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
distributing each characters on a cell | New Users to Excel | |||
distributing each characters on a cell | Excel Programming | |||
Distributing a Cell Value | Excel Worksheet Functions | |||
Distributing values from rows to columns for Access import | Excel Worksheet Functions | |||
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming |