Google-sheets – How to combine 2 sheets with expanding columns

google sheetsworksheet-function

I'm trying to combine 2-6 data sets that has been randomized using query so that I can sort them for analysis.

Here is a sample of the data with the desired result

I tried using {A:A;B:B} but it only combines rows.

I having a hard time generating new columns or separating new data that is still coming in.

In the sample data in the link above, user 1,2,3 ends up in the same columns with user 4,5,6 when each user should be their own columns.

The columns for Epic and Story are unique indentifiers for the questions. Epic for a group of questions while Story is an ID for a single question. I was going to use them to group up the questions.

Best Answer

You can try this on your example sheet :

={B2:J10;
  {B14:D21,H14:J21,E14:G21}
 }

If you have multiple sheet, try something like this

= { sheet!1 data +     
     { sheet2! > first three identifiers  + empty columns + sheet2!data }
}

then you can combine data, in your example sheet :

=sum(
FILTER(O$13:O$28,
$N$32:$N$47=$N13,
$M$32:$M$47=$M32,
$L$32:$L$47=$L32)
)

General solution is :

= sum ( 
filter ( data , 
        filter 1 > question 
        filter 2 > story 
        filter 3 > epic 
    )

Overview

The OP basically wants to be able to use the spreadsheet functions over the original data without having to scroll across a large number of rows and columns. His background make him think about having custom views and a easy way to change from one to another but Google Sheets doesn't have a built in feature to manage custom views.

Fortunately the fourth requirements are doable by using built-in features and Google Apps Script

Rows could be hidden by using filters but columns only could be hidden manually or by using code.

For 1, and 2 use Google Apps Script to create custom menus that apply the hide/show columns and optionally rows over the "master sheet". This will allow the user to edit the data directly over the "master sheet" and sort it.

Code example

The following code creates a menu for handling the hide/show operations of columns specified in a global variable.

var cols = [1,5,7]; //columns to be hidden/shown

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ 
    {name: "Hide", functionName: "hideCols"},
    {name: "Show", functionName: "showCols"}
    ];
  ss.addMenu("Custom Views", menuEntries);
}

function hideCols() {
  var sheet = SpreadsheetApp.getActiveSheet();
  cols.forEach( function(col) {
      sheet.hideColumns(col);
    }
  )
}

function showCols() {
  var sheet = SpreadsheetApp.getActiveSheet();
  cols.forEach( function(col) {
      sheet.showColumns(col);
    }
  )
}

Best Answer

Related Solutions

Google-sheets – Google Spreadsheet: Adding formulas to Google Forms responses rows

Google-sheets – Multiple views of master spreadsheet that allow editing of master data

Overview

Code example

Related Topic