Forum Discussion
Seedling distribution
ms_mit Great you could get it to work. And what's 15 minutes for a job that would otherwise take perhaps a whole day of copying and pasting. Now that I look back at the query, I see I was over-thinking the matter by adding the Grouped Running Total. We can do without and it will probably speed up the process. See attached.
Do you want to calculate the number of bags and the number of remaining seedlings? I applied the following formulas to do this:
=SUMIFS($D$6:$D$34,$C$6:$C$34,J4,$A$6:$A$34,I4)
=ROUNDDOWN(K4/$B$3,0)
=MOD(K4,$B$3)
Could you communicate the formulas to assign bag numbers?
OliverScheurichFor some strange reason, I received your latest solution by email notification but it is not shown in the comments of this thread. And no sample file is here to download. Anyway I did paste in your "Seedling" code and the formula for doing the bag numbers. I found that it works for all the seedling quantities that are 25 or more. Any quantities below 25, it ignores....which also throws off the bag number formula. If you have a solution for that, I would really appreciate it.
Another strange thing is that When I run this on an almost full data set (31,000 rows) the code throws an overflow error on the line "z = z + 1". Alternately, I I run the full data set (33,764 rows), after changing the line "For i = 4 To 33764", it immediately gives an overflow error. I guess I could break the data set up and run separate smaller sets but it would be interesting to know why this happens.
I have attached a file with some actual data in it, so you can see what happens with the problem of ignoring the <25 quantities.