 So, I have a data where several columns have the same column names. I would like to arrange these column names alphabetically and then combine the entries of columns with the same column name into a single column.

I have tried vlookup and querry functions but to no avail.

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 `TRANSPOSE` twice.

``````={A1:C1,transpose(sort(unique(transpose(D1:K1)),1,true))}
``````

Then for the data in A2:C5 I used another array : `={A2:C5}`

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 :

``````QUERY(TRANSPOSE(\$A\$1:\$K\$5),
``````

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))`

Note : I've highlight cells who contain formula, + next time try to provide a sample.