Creating new row(s) from data held in a single row

stuartr

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, j.smith@123.co.uk, 25 New Street, 3 (trees in total) 26 cherry, 33 oak, 45 silver birch, Y/N into three separate rows of

John Smith, j.smith@123.co.uk, 25 New Street, 26 cherry, Y/N

John Smith, j.smith@123.co.uk, 25 New Street, 33 oak, Y/N

John Smith, j.smith@123.co.uk, 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.😃

Andrée Starå

Hi @stuartr

I hope you're well and safe!

As a workaround, you could maybe use my method below.

I developed a solution using two forms (or the same form by using conditional logic) that can be used to submit the main information and then the other information on a new row for each submission after the first.

• Form 1, fill in, John Smith, j.smith@123.co.uk, 25 New Street
• Form 2 loads automatically, fill in, 26 cherry, Y/N
• Form 2 reloads, fill in, 33 oak, Y/N

They will be grouped with conditional formatting in the sheet.

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

stuartr

Hello (again) @Andrée Starå

Thank you for your idea. From your example above, I have effectively already built form 1 anyway, but seems you are suggesting I create a 2nd (reduced) form that just asks 2nd tree location and species, and keeps reloading everytime they want to add another tree and location? I had some questions

1) Would there be anyway of only presenting form 2 based on the number of total trees they want to plant. ie they select 5 trees so the logic only presents form 2 a further 4 times

2) How do I get the 2nd (different) form to load automatically

3) How would I be able to link the details of multiple trees captured via form 2 back to john smiths name and address info captured in form 1.

I'm happy to do the work, but if I give you a link to the form and a share to the sheet would that help you see what you have to work with? Stuart

stuartr

@Andrée Starå I had another idea (or two) and would like to run them past you. How about creating an automation that would copy the whole row into a second (duplicate) sheet when there is a value/is not null in tree location one. Then duplicate the automation and change to tree location 2, 3 etc to copy all other appropriate rows into the new sheet. Sorting the new sheet by email address (as a unique value) would group all requests back together. I would then need to manually delete tree location 1 details on the 2nd row, and tree locations 1 and 2 on the 3rd row etc?

Or could you create one copy row automation but run it multiple times based on the value in the total number of tree locations. This may not be possible, but I thought you might know? Thanks in advance as ever. Stuart