Copy rows and update cells macro

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I wonder if anyone could help me with this particular problem.

I have a spreadsheet with a column that has a name and another column that has a quantity.

What I'd like to be able to do is that where there is an instance that a row contains a quantity greater than 1 (all whole numbers), that a new row would be added with a quantity equal to 1 and a quantity of 1 deducted from the original row, looping through the list until the orginal row's quantity is equal to 1 and the sum of the quantitites of the copied rows add up to the oriinal value.

Example;

start
row Name qty
2 test 5

finish
start
row Name qty
2 test 1
3 test 1
4 test 1
5 test 1
6 test 1

Of course, I used this model for simplicity. My spredasheet has about 11 columns of data, but the logic here would still apply.

Any help would be appreciated.





1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
perhaps most easy route would be
1. read the value of column C (5 as per your example)
2. Add 4 lines below
3. number the row and change the value to 1 in column c


you can record your action to get the macro