Creating new row(s) from data held in a single row
Hello (again) smartsheet community. I hope someone can help advise with the following challenge for a charity that helps to plant more trees in urban areas.
We use a form to collect a contact name, email address, street name, number of trees they want to plant in total, then the location & species of each tree. Once we have all this data from all the wards (surrounding areas), it needs to be shared with the local council who will then scan and check each proposed tree location in turn and give us a yes or no decision as to whether a tree can be planted in each proposed location.
So what is the easiest way to transform this information from a single row into separate rows before we then forward it to the council to allow them to select yes or no in a "council decision" column?
Some rows will only contain a single proposed tree location, so these are straightforward, but many rows will contain multiple tree locations and more than we need because the council often reject about 20% of all proposed sites. To put it another way how do I turn a single row of
John Smith, [email protected], 25 New Street, 3 (trees in total) 26 cherry, 33 oak, 45 silver birch, Y/N into three separate rows of
John Smith, [email protected], 25 New Street, 26 cherry, Y/N
John Smith, [email protected], 25 New Street, 33 oak, Y/N
John Smith, [email protected], 25 New Street, 45 silver birch, Y/N
I can export all data into excel and manipulate it, but there will be 1500 rows, so I'm wondering if there is a smarter way that automation, copy cells, update value in a cell etc might help.
Thank you in advance.😃