SOLVED

New Contributor

# 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.

5 Replies

# Re: Data conversion

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.

# Re: Data conversion

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?

best response confirmed by ms_mit (New Contributor)
Solution

# Re: 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.

# Re: Data conversion

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.

# Re: Data conversion

@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.