Jul 16 2020 01:20 AM
I am working with some list of trees we planted with our little non-profit and I need a little snippet of code to automate the expansion of a list of fields with trees planted to a list of individual trees. I have field data that looks like this:
Plot_id | Qty_Trees | Species | Coordinate |
11001 | 2 | 2 | S7 38.593 E109 40.2 |
11001 | 4 | 6 | S7 38.593 E109 40.2 |
11008 | 2 | 3 | S7 38.593 E109 40.3 |
I need to convert this to a list that looks like this:
ID | Plot_id | Qty_Trees | Species | Coordinate |
1 | 11001 | 1 | 2 | S7 38.593 E109 40.2 |
2 | 11001 | 1 | 2 | S7 38.593 E109 40.2 |
3 | 11001 | 1 | 6 | S7 38.593 E109 40.2 |
4 | 11001 | 1 | 6 | S7 38.593 E109 40.2 |
5 | 11001 | 1 | 6 | S7 38.593 E109 40.2 |
6 | 11001 | 1 | 6 | S7 38.593 E109 40.2 |
7 | 11008 | 1 | 3 | S7 38.593 E109 40.3 |
8 | 11008 | 1 | 3 | S7 38.593 E109 40.3 |
In other words, I need to make a new row for each tree, carrying over the species and coordinate data. My initial list is a long one (around 1700 records) so doing is manually, is a bit of a chore and somewhat error prone. My coding skills are nil but I can cut and paste things. If anyone could help steer me in the right direction, I would very much appreciate it.
Thanks in advance
Jul 16 2020 02:20 AM
The details depend upon the version of Excel you are using. The key is to accumulate the total number of trees from row to row of your original table. Then an approximate match of the row number (ID) in the new table will determine which row of the source table to link to for additional data.
Jul 16 2020 03:10 AM
Thanks much for the solution. This is really perfect but I have one problem. My version of 365 only gives me Excel 2016. Do you have a solution that works with just plain MATCH?
Jul 16 2020 09:46 AM
SolutionTo work with MATCH, the accumulated count needs to be the first number associated with the plot, rather than the final count.
Whilst you are refactoring your data, I would suggest that the best data structure would be to have one table that relates to individual trees and use a field to reference a second table which relates to plots. The number of trees of a given species growing within a plot would then be a calculated quantity using COUNTIFS to search the tree table rather than being input data that controls the tree table as output.
Jul 16 2020 09:33 PM
Hello@Peter Bartholomew
Thanks for this. It is a perfect solution for us. This is actually a process of preparing our data to be uploaded into a database. We had an automated system but it recently broke and the original developer is no longer with us...so stopgap with Excel until our newer staff have time to fix it.
In actual practice, we do keep the plot data and trees data in separate tables...in fact a lot of related tables. This is just a temporary aggregation to get the job done. I appreciate your suggestions.
Thanks again, This is a good solution.
Jul 17 2020 10:55 PM
@ms_mit , Here's a Power Query variation. After you add additional rows to source table, click on Data->Refresh All to get the formatted data on the right side.
Jul 16 2020 09:46 AM
SolutionTo work with MATCH, the accumulated count needs to be the first number associated with the plot, rather than the final count.
Whilst you are refactoring your data, I would suggest that the best data structure would be to have one table that relates to individual trees and use a field to reference a second table which relates to plots. The number of trees of a given species growing within a plot would then be a calculated quantity using COUNTIFS to search the tree table rather than being input data that controls the tree table as output.