Forum Discussion

ms_mit's avatar
ms_mit
Copper Contributor
Oct 27, 2021
Solved

Seedling distribution

I am looking for some Excel help to help us organize a tree planting plan for a project we are doing. We have base data that includes farmers' names, the species of seedlings they want to plant and t...
Formulas and Functions
Macros and VBA
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Oct 29, 2021

    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.

     

     

     

     

ms_mit's avatar
ms_mit
Copper Contributor
Oct 27, 2021
Hi Jitin, Thanks for the help. Pivot tables are a wonderful thing but I also tried using them and couldn't get it. The problem I am working on is finding a formula (or set, or some code), to parcel these gross amounts (i.e. 80 mahogany for a particular farmer) into individual bags of 25 seedlings, or some limit like that. So in other words, I'd end up with 3 bags of 25 mahogany each, with a remainder of 5 which would go in the next bag of 5 mahogany and 20 teak. Does that make sense?
  • ms_mit's avatar
    ms_mit
    Copper Contributor
    Oct 28, 2021
    Hi Jitinm, Thanks for that. It calculates the full bags (of 25) and the remainder correctly. Do you have any idea how to enumerate a list with the individual line items? So for example Farmer1 + Mahogany = 3 bags of 35 seedlings each becomes:
    Farmer1 | Mahogany | 25
    Farmer1 | Mahogany | 25
    Farmer1 | Mahogany | 25
    .....?
    • jitinm's avatar
      jitinm
      Iron Contributor
      Oct 28, 2021
      Can you share working in excel for example how it should look. That would help much.
      • ms_mit's avatar
        ms_mit
        Copper Contributor
        Oct 28, 2021

        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 NameTree SpeciesQty of Seedlings
        Farmer 1Mahogany75

         

        I need to find formulas or VBA to transform this into a list like this:

        Farmer NameSpeciesSeedling qty per bagBag No.
        Farmer 1Mahogany251/10
        Farmer 1Mahogany252/10
        Farmer 1Mahogany253/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

Resources