Google-sheets – Combine Google sheets data with the same header names

google sheets

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.

Best Answer

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.

Related Solutions

Google-sheets – Working with names in Google Sheets

If the names are in one column and the times in another the sumif() formula works well:

SUMIF(range, criteria, sum_range) Adds the cells specified by a given criteria. Range is the range to which the criteria are to be applied. Criteria is the cell in which the search criterion is shown, or the search criterion itself. In the criteria section, you can use a question mark (?) to match a single character or an asterisk () to match a sequence of characters. If you'd like to find an actual question mark or asterisk, you can type a tilde (~) before the question mark (?) or asterisk (). Sum_range is the range from which values are summed, if it has not been indicated, the values found in the Range are summed.

The other option is a pivot table. Which is more complex.

If the names and data are in the same column, a lot more work will be needed.

Google-sheets – Summing values from multiple sheets with names specified in cells

A spreadsheet formula cannot reference an undetermined number of sheets. The logic of "count the occurrences of this value in this range in all sheets" can be expressed in a custom function such as

=countinsheets("A:B", "Bananas", 1)

where the function is

function countinsheets(range, value) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var result = 0;
  for (var i = 0; i < sheets.length; i++) {
    var data = sheets[i].getRange(range).getValues();
    for (var j = 0; j < data.length; j++) {
      for (var k = 0; k < data[0].length; k++) {
        if (data[j][k] == value) {
          result++;
        }
      }
    }
  }
  return result;
}

This finds all occurrences of "Bananas" in columns A:B, in all sheets, and returns their number. Note that the range "A:B" is passed as a string, which is necessary in this case, but has a major drawback.

The drawback is: the function value will not auto-update when the spreadsheet data is changed. One has to force a refresh by changing the third parameter of the function, which is a fake parameter introduced precisely for that reason. Change 1 to something else to make the function recalculate; it will not do so on its own.

Best Answer

Related Solutions

Google-sheets – Working with names in Google Sheets

Google-sheets – Summing values from multiple sheets with names specified in cells

Related Topic