Filtering data by row Google sheets

Question

I have a google sheet populated from a google form. It gives the possibility in the form to enter multiple names in the same entry but I'd like to filter in a tab keeping all data but having a name per row in the sheet. For example: >

I would like a formula that filters the data as follows:

Here is a test sheet showing the details: Test sheet

Anyone could help with a formula like this?

Answer 1

Here's one approach you may test out:

=let(Σ,tocol(,1), reduce(Σ,'Form responses 1'!C2:index('Form responses 1'!P:P,match(,0/('Form responses 1'!A:A<>""))),lambda(a,c,vstack(if(iserror(a&""),Σ,a),
        if(len(c)*(not(isbetween(column(c),4,7))),hstack(index('Form responses 1'!A:B,row(c)),c,index('Form responses 1'!D:F,row(c))),Σ)))))