3

I would like to place some values in a table, given their row and column coordinates. Do I have to use a script or can I use a function?

On the image I swapped the x and y axis but I think you get the point.

sample data

  • Hello, could you post your custom script here? I would really appreciate it if you did! Thanks! – Choksiri P. Apr 11 '16 at 15:27
1

I could think of four approaches.

  1. Fill the sheet with single-cell vlookup formulas looking up values in that table for their row/column. Drawback: many lookups, most of which will be fruitless. The performance is likely to be poor.
  2. Try to do (1) with an arrayformula. Drawback: I don't see how to implement that purely in Sheets logic.
  3. Have a custom function returning an array. Drawback: a custom function can only return a rectangular array. The table would be non-editable directly, as an attempt to edit it would result in the output of custom function failing to expand.
  4. A script triggered from the menu. This is what I did below.

The script adds a menu command "write a table". To use it, first select the range and then invoke the command.

function onOpen() {
  var menu = [{name: "Write a table", functionName: "write"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

function write() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var inp = SpreadsheetApp.getActiveRange().getValues();
  for (var i = 0; i < inp.length; i++) {
    Logger.log(inp[i][0]+inp[i][1]);
    sheet.getRange(inp[i][0]+inp[i][1]).setValue(inp[i][2]);
  }
}
1

Create crosstab report using an array formula

Use the following formulas:

Cell B8 : Column Headers

=transpose($B$3:$B$5)

Cell A9 : Row Headers

={$A$3:$A5}

CELL B9 : cross tabbed values

=ArrayFormula(IFERROR(vlookup({A9:A11&B8:D8},{A3:A5&B3:B5,C3:C5},2,0))))

Explanation

  • {A9:A11&B8:D8} creates an array of keys by joining the values of the X (row headers) and Y (column headers) columns.
    Note: As the first element is 3 X 1 array and the second is a 1 X 3 array, the result is a 3 X 3 array.

  • {A3:A5&B3:B5,C3:C5} creates an array having two columns. The first column holds the keys and the second the values.
    Note: In the concatenation, the first element is a 3 x 1 array and the second also is a 3 x 1 array so the result is a 3 x 1 array.

  • vlookup will return a value for each match and error for each cell without a match.

  • IFERROR will replace errors by empty values.
  • ArrayFormula extends the array of values to the required cells.

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.