Forum Discussion
Data conversion
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.
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.
HelloPeterBartholomew1
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.
