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.
ms_mit Since you mention to have 1.3 million seedling to distribute to 8000 farmers, you should look into Power Query. Played around a bit with your example and came up with a "solution" that needs refinement. Half of it is in PQ. For the other half I cheated a bit by adding some manual columns in Excel. Should be possible to do those in PQ as well but have the inspiration right now.
Thanks again!
ms_mit Well, if you are completely unfamiliar with PQ, this is not very straight-forward. The file I uploaded has quite some "non-stadard" coding, and is not really a finished product. Not sure if you are going to master it if you are in a hurry. What you could try is take the attached file and dump your real data in the structured table called "Table1" with exactly the same column names. Then press Refresh on the Data ribbon and see what happens.
More about the PQ basic here:
https://exceloffthegrid.com/power-query-introduction/
Thanks Riny, This gets me on the road to the solution. I have used PQ in the past for some simple aggregation of data from multiple worksheets but nothing like the transforms you are doing here. Admittedly, I don't understand the queries you created, especially the steps in the Append1 and what the GRT function does but I will eventually figure it out.
I did run this query with a full dataset. Takes about 15 minutes to run even with a relatively powerful computer. But it works. Now I just have to figure out how to transpose the species and seedling count for the mixed bags.
Thanks also for the link to the PQ tutorial. It looks like a good one.
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.