Merging several form questions into one column

amy_ilearning

Hi! I have created a form but am having some issues with how fields from the form are collected and reported to employees.

The form is a sign-in for sessions. Attendees select their presenter from several lists (e.g. A - C, D - F). These presenter fields are set up as Contact Lists and linked to our users, and each form can only have ONE presenter selected.

Form responses are then collected in a sheet. Each of the options (e.g. A - C, D - F) has their own column, and those columns are formatted as Contact Lists. I want to combine all the columns into one, and because one presenter can only ever be selected, that column will only ever have one entry.

I tried the =(A - C@row + D - F@row) formula as well as as the =JOIN formula, but the entry column only provides the presenter name as text, not as a contact (see screenshot). Both the source column (A - J) and the destination columns are formatted as Contact Lists and don't allow more than one.

The entries are then captured in a report and filtered based on Current User in the mapped column. But this doesn't work because the column with the formula doesn't result in a Contact, only text.

My question is: Is there a way to have all selections from certain form fields automatically merge into one column? I don't really need the source sheet to have all the different A - C, D - F columns because there will only ever be one selection. I don't see in the form a way to merge columns, and the source sheet is messy with all the different option columns.

If not, how do I get the merge column (the one with the formula) to display the result as a contact?

Paul Newcome

The formula in the sheet would be along the lines of:

=INDEX(COLLECT([First Contact Column]@row:[Last Contact Column]@row, [First Contact Column]@row:[Last Contact Column]@row, @cell <> ""), 1)

amy_ilearning

Dang it, you're good! That was exactly it! Thank you, Paul - you're always a lifesaver :)

Paul Newcome

Happy to help. 👍️