Forum Discussion
Data conversion
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
To 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.
5 Replies
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.
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?
To 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.
