Combine Google sheets data with the same header names

Question

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.

Answer 1

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.