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.
Hijitinm I have cleaned up the formating a bit to hopefully make it easier to understand. Here is the idea basically. I have the 'Base Data' you see in the table on the right. I have to transform this data to a list like what you see in the 'Results Data' on the left. The transform goes roughly like this: The first line of the Base data is:
| Farmer Name | Tree Species | Qty of Seedlings |
| Farmer 1 | Mahogany | 75 |
I need to find formulas or VBA to transform this into a list like this:
| Farmer Name | Species | Seedling qty per bag | Bag No. |
| Farmer 1 | Mahogany | 25 | 1/10 |
| Farmer 1 | Mahogany | 25 | 2/10 |
| Farmer 1 | Mahogany | 25 | 3/10 |
But then it gets more complicated. Some species are not divisible by 25 (max seedlings per bag) so there is a remainder. This means we have to group one of more species of seedlings into a single bag, so it means one bag has more than one row of data (but it still has the same bag number). Does that make sense? Let me know if you have another idea. The data could also be arranged with the multi species bags in more than one column. At the end, this results data will be used to print labels for all the bags so workers can go and fill the bags with the correct seedlings
- Understood, there must be some additional column in your base data which specifies which one has single bag or multiple bag. I mean the vba I shared added multiple columns and I used some logic to distribute the qty as per 25 rule( I hope my vba solved your first/earlier query?). So to create additional logics there must be some input in your base data to do.