Split data into several rows of same data

Valerija_0201 Posts 1 Registration date Sunday January 13, 2019 Status Member Last seen January 13, 2019 - Updated on Jan 13, 2019 at 02:19 PM
 Blocked Profile - Jan 14, 2019 at 05:03 PM

I would like to split a row of data into several rows of same data in MS Excel. I will try to explain the situation.

I need a macro that will divide the work order into several work orders in such a way that the maximum quantity per work order is 50.000 m but that the divided quantity gives full boxes.

For example, if the work order S01463541 is for 130.500 m, macro should divide it into 3 parts: 2 x 49.500 m (49.500 m / 1.500 m per box gives 33 boxes) + 1 x 31.500 m (31.500 m /1.500 m per box gives 21 boxes). Also, when dividing the work order the first work order should remain the same and the following work orders should be + 1. In other words, if the work order is S01463541 the next one should be S01463542, S01463543, S01463544 and so on.

Work Order Product Total Quantity Meters per box Total Boxes
S01463541 500019998 130.500 1500 87
S01464311 500020000 115.500 1500 77
S01464251 500019996 175.500 1500 117
S01464401 500020018 201.600 2400 84
S01464381 500020006 90.000 1200 75
S01464361 500020003 200.400 1200 167
S01461541 500067149 100.800 600 168

Thanks a lot in advance.


1 response

Blocked Profile
Jan 14, 2019 at 05:03 PM
So, you really need a database, not an excel spreadsheet! It will AUTO INCREMENT as you are asking. You can set it up to do your calculations for each BOX! If you don't know how to make a macro that will do this, you are on the same level of knowledge as with a DATABASE, and you get ALOT further using a solution that will SCALE! Have fun with that on an excel sheet!