I've searched everywhere and I could't find this solution before, so I apologize if this was already answered.

I'm currently building a form in Google Forms to request Full Names and Death Dates (for religious purposes) and, aiming to improve User Experience, I'm formatting the field as a Long Answer field with the following regular expression to ensure the person answering the form will separate the names and dates with commas:

• [A-Za-z] \s[0-9]{2}/[0-9]{2}/[0-9]{2}$,*

When the person send the response, it's formatted like this in a cell |Full Name and Death Date| |-| |Maria da Silva 27/05/22, Juliana da Silva 26/05/22, Leonardo da Silva 28/05/21|

I want to have this result instead, so I can sort people deceased in the last 49 days only: |Full Name|Death Date| |-|-| |Maria da Silva|27/05/22| |Juliana da Silva|26/05/22| |Leonardo da Silva|28/05/21|

Currently, I'm using the following formula to capture the information of a single cell

=TRANSPOSE(SPLIT(H6;", ";false))

Where H6 is the reference of the cell I want to transpose.

When I drag down the selection along the column, I get the #REF! error when the results overlap. How can I stack the results in a single column without having this error?

Here is the table link and here is the Form link. I'm sorry, but it's currently in Brazilian Portuguese only, I plan to create new versions in English and Spanish as well in the future.

Also, I don't know if that's possible, but I want to avoid repeated names in the list, so, in the future, I want to create like a search field or something in Google Forms when the person is filling it to show similar results or autocomplete if that name already exists so either the name is excluded from list with a Unique function or the form doesn't let the person insert that name again.

Thanks in advance!

CodePudding user response：

use:

=INDEX(FLATTEN(TRIM(SPLIT(TEXTJOIN(","; 1; H4:H); ","))))

or:

=INDEX(QUERY(FLATTEN(TRIM(IFERROR(SPLIT(H4:H; ",")))); 
 "where Col1 is not null"; ))