Make Single Line Form Entry into 18 Separate Rows

kcaudill1989

I have created a process using Helper Columns, Formulas and Automation that takes a 1 row form entry copies it into 18 separate rows based on the Sport Column and then copies those 18 rows into a 2nd sheet data sheet and then moves the entered row (Row 1) to an archive sheet.

The theory was that when someone submits a form response it is entered in Row 1of the (Response Sheet), then the formulas copy the information to the separate rows below Row 1 and then the automation copies the 18 rows to the 2nd sheet (Data Sheet) and the another automation moves Row 1 to an Archive Sheet.

This is the problem. It seems that if too many responses are submitted to close together then it doesn’t have time to complete the process and therefore some submissions don’t go through the whole process.

Does anyone have an idea of how I make this work for all submissions or how else I could accomplish this task.

Here are the formulas I am using:

To make one row into 18 separate rows by sport I am using the following cell formulas in 18 rows, I have cell formulas for each column that needs to be copied to the Officials Reg Numbers – Data Sheet.

Submission Number - =IF(Officials@row <> "", MAX([Submission #]:[Submission #]))

Year – =INDEX([Form Year]:[Form Year], 1)

Name – =INDEX([Form Name]:[Form Name], 1)

Email – =INDEX([Form Email]:[Form Email], 1)

State – =INDEX([Form State]:[Form State], 1)

Sport – Hard coded with each sport there is a column for (Baseball, Basketball, etc.) and then used in Officials formula

Officials – =INDEX(Basketball:Basketball, 1)

Submission # - Hard coded with numbers 1-18

Then I have the below Column Formulas and Automation that work on Row #1, the row the submission is written to.

Helper Row:

=[Submission #]@row

Check Archive:

=IF(HAS({Officials Reg Numbers - Submission Number}, Helper@row), 1, 0)

Automation – Copy to Data Sheet:

Trigger - When rows are changed

When “Submission Number” changes to Any Value

AND Where “Submission Number” is not blank

Copy Rows to “Officials Reg Numbers – Data Sheet

Automation – Move Submission to Archive

Trigger – When rows are changed

When “Archive” changes to Checked

Move rows to Officials Reg Numbers - Archive

Here are a couple images of my sheets:

Submission/Processing Sheet (I have hidden the submission sport columns, except one) just to make the screenshot manageable.

Final Data Sheet

Archiduc

Have you considered running the automation at regular intervals rather than "When rows are changed" I would recommend running the process out of hours if it's not too time sensitive.

An alternative I've never tried is to rank the submission, with one of the rank formulas (I've not used them so I cannot help much sorry) and have the automation filtered on submission rank 1, perhaps every hour as it's the shortest timespan.