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