First you need to combine column's names. To do so I used an array with
Then i need to have unique names and sort in alphabetical order. These type of functions works when data is in column, that's why there's a
Then for the data in A2:C5 I used another array :
Now comes your main problem, retrieve data for each column and combine when there's two thanos for example.
QUERY works in this case but i need to check rows and not column so i used another transpose function for the range :
Then for the SQL request, SELECT Col2 (so select the row corresponding) WHERE Col1 (row 1 who contains the students names i.e. Thanos) = name of the student.
"SELECT Col"&(ROW($A9)-ROW($A$8)+1)&" where Col1 = '"&D$8&"'"
Then lock the columns or row with $ and now you can expand the formula everywhere.
I added a
TEXTJOIN because sometimes there is two results (normal one + a blank one), moreover it'll be a good way to monitore the spreadsheet for problems.
The final function :
=textjoin(",",TRUE,QUERY(TRANSPOSE($A$1:$K$5),"SELECT Col"&(ROW($A9)-ROW($A$8)+1)&" where Col1 = '"&D$8&"'",false))
Here's a sample sheet, it was a fun problem.
Note : I've highlight cells who contain formula, + next time try to provide a sample.