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.
Hey OliverScheurich, thanks for helping
My formula for assigning bag numbers is
=COUNTIF($A$6:A6,A6)&"/"&ROUNDUP(SUMIF($I$3:$K$14,A6,$K$3:$K$14)/$B$3,0.1)
But it is just counting instances of the Farmer name. This only works until I have a bag with more than one species in it, then it breaks.
Your formulas are, I think, approaching it the other way around. The data I showed is example only. What I am trying to calculate is the lists in columns A:D. I only have the data in the base data columns I:K. Nearly 20,000 rows of it that I have to parcel out into a list like A:D.
I am sure there is a way, it's just really testing my excel skills.
