SOLVED

Data conversion

%3CLINGO-SUB%20id%3D%22lingo-sub-1525831%22%20slang%3D%22en-US%22%3EData%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525831%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20with%20some%20list%20of%20trees%20we%20planted%20with%20our%20little%20non-profit%20and%20I%20need%20a%20little%20snippet%20of%20code%20to%20automate%20the%20expansion%20of%20a%20list%20of%20fields%20with%20trees%20planted%20to%20a%20list%20of%20individual%20trees.%20I%20have%20field%20data%20that%20looks%20like%20this%3A%3C%2FP%3E%3CTABLE%20width%3D%22333%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3EPlot_id%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EQty_Trees%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ESpecies%3C%2FTD%3E%3CTD%20width%3D%22141%22%3ECoordinate%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11001%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3ES7%2038.593%20E109%2040.2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11001%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3ES7%2038.593%20E109%2040.2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11008%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3ES7%2038.593%20E109%2040.3%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20convert%20this%20to%20a%20list%20that%20looks%20like%20this%3A%3C%2FP%3E%3CTABLE%20width%3D%22397%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3EID%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EPlot_id%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EQty_Trees%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ESpecies%3C%2FTD%3E%3CTD%20width%3D%22141%22%3ECoordinate%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E11001%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3ES7%2038.593%20E109%2040.2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E11001%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3ES7%2038.593%20E109%2040.2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E11001%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3ES7%2038.593%20E109%2040.2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E11001%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3ES7%2038.593%20E109%2040.2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E11001%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3ES7%2038.593%20E109%2040.2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E11001%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3ES7%2038.593%20E109%2040.2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E11008%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3ES7%2038.593%20E109%2040.3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E11008%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3ES7%2038.593%20E109%2040.3%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20other%20words%2C%20I%20need%20to%20make%20a%20new%20row%20for%20each%20tree%2C%20carrying%20over%20the%20species%20and%20coordinate%20data.%20My%20initial%20list%20is%20a%20long%20one%20(around%201700%20records)%20so%20doing%20is%20manually%2C%20is%20a%20bit%20of%20a%20chore%20and%20somewhat%20error%20prone.%20My%20coding%20skills%20are%20nil%20but%20I%20can%20cut%20and%20paste%20things.%20If%20anyone%20could%20help%20steer%20me%20in%20the%20right%20direction%2C%20I%20would%20very%20much%20appreciate%20it.%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1525831%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525895%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F730392%22%20target%3D%22_blank%22%3E%40ms_mit%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EHi%2C%20consider%20using%20Power%20Query%20for%20this.%20What%20you%20need%20to%20do%20is%20mark%20your%20list%20and%20then%20navigate%20to%3A%20Data%20-%20From%20Table%2FRange.%20When%20prompted%20with%20the%20%22Create%20Table%22%20message%20box%2C%20make%20sure%20to%20tick%20the%20%22My%20table%20has%20headers%22%20box.%3CBR%20%2F%3E%3CBR%20%2F%3EWhen%20pressing%20OK%2C%20you%20will%20be%20directed%20to%20the%20Query%20editor%2C%20where%20you%20can%20navigate%20to%20Add%20column%20and%20then%20press%20Index%20column%20(use%20the%20drop%20down%20to%20choose%20Start%20from%201).%20Afterwards%20you%20can%20drag%20the%20newly%20created%20index%20column%20to%20the%20left%20side%20of%20the%20table.%3CBR%20%2F%3E%3CBR%20%2F%3EThen%20press%20Close%20%26amp%3B%20Load%20from%20the%20Home%20tab%20and%20you%20are%20all%20set!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525945%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F730392%22%20target%3D%22_blank%22%3E%40ms_mit%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20details%20depend%20upon%20the%20version%20of%20Excel%20you%20are%20using.%26nbsp%3B%20The%20key%20is%20to%20accumulate%20the%20total%20number%20of%20trees%20from%20row%20to%20row%20of%20your%20original%20table.%26nbsp%3B%20Then%20an%20approximate%20match%20of%20the%20row%20number%20(ID)%20in%20the%20new%20table%20will%20determine%20which%20row%20of%20the%20source%20table%20to%20link%20to%20for%20additional%20data.%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1526047%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1526047%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20much%20for%20the%20solution.%20This%20is%20really%20perfect%20but%20I%20have%20one%20problem.%20My%20version%20of%20365%20only%20gives%20me%20Excel%202016.%20Do%20you%20have%20a%20solution%20that%20works%20with%20just%20plain%20MATCH%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1527055%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1527055%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F730392%22%20target%3D%22_blank%22%3E%40ms_mit%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20work%20with%20MATCH%2C%20the%20accumulated%20count%20needs%20to%20be%20the%20first%20number%20associated%20with%20the%20plot%2C%20rather%20than%20the%20final%20count.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhilst%20you%20are%20refactoring%20your%20data%2C%20I%20would%20suggest%20that%20the%20best%20data%20structure%20would%20be%20to%20have%20one%20table%20that%20relates%20to%20individual%20trees%20and%20use%20a%20field%20to%20reference%20a%20second%20table%20which%20relates%20to%20plots.%26nbsp%3B%20The%20number%20of%20trees%20of%20a%20given%20species%20growing%20within%20a%20plot%20would%20then%20be%20a%20calculated%20quantity%20using%20COUNTIFS%20to%20search%20the%20tree%20table%20rather%20than%20being%20input%20data%20that%20controls%20the%20tree%20table%20as%20output.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1528237%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1528237%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20this.%20It%20is%20a%20perfect%20solution%20for%20us.%20This%20is%20actually%20a%20process%20of%20preparing%20our%20data%20to%20be%20uploaded%20into%20a%20database.%20We%20had%20an%20automated%20system%20but%20it%20recently%20broke%20and%20the%20original%20developer%20is%20no%20longer%20with%20us...so%20stopgap%20with%20Excel%20until%20our%20newer%20staff%20have%20time%20to%20fix%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20actual%20practice%2C%20we%20do%20keep%20the%20plot%20data%20and%20trees%20data%20in%20separate%20tables...in%20fact%20a%20lot%20of%20related%20tables.%20This%20is%20just%20a%20temporary%20aggregation%20to%20get%20the%20job%20done.%20I%20appreciate%20your%20suggestions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%2C%20This%20is%20a%20good%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1530590%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1530590%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F730392%22%20target%3D%22_blank%22%3E%40ms_mit%3C%2FA%3E%26nbsp%3B%2C%20Here's%20a%20Power%20Query%20variation.%20After%20you%20add%20additional%20rows%20to%20source%20table%2C%20click%20on%20Data-%26gt%3BRefresh%20All%20to%20get%20the%20formatted%20data%20on%20the%20right%20side.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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_idQty_TreesSpeciesCoordinate
1100122S7 38.593 E109 40.2
1100146S7 38.593 E109 40.2
1100823S7 38.593 E109 40.3

 

I need to convert this to a list that looks like this:

IDPlot_idQty_TreesSpeciesCoordinate
11100112S7 38.593 E109 40.2
21100112S7 38.593 E109 40.2
31100116S7 38.593 E109 40.2
41100116S7 38.593 E109 40.2
51100116S7 38.593 E109 40.2
61100116S7 38.593 E109 40.2
71100813S7 38.593 E109 40.3
81100813S7 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

5 Replies

@ms_mit 

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.

 

 

Hi @Peter Bartholomew 

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

@ms_mit 

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.

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.

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