1

sample data

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.

  • 1
    Welcome. Please add a brief description of your search/research efforts as is suggested on How to Ask. – Rubén Aug 5 at 13:03
  • 1
    Please edit the question to add those details. – Rubén Aug 5 at 13:05
0

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.

  • Perfect! This is exactly what I was looking for! Thank you. – dex Aug 6 at 3:45
  • Great, you can accept this as the correct answer :) Have a great day. – pjmg Aug 6 at 6:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.