Google-sheets – Automatically create an array of unique names and their number of wins

google sheets

I'm trying to make a sheet that I can use to track which heroes I need to practice in a game…
Want to manually input the following things:

Map name (A2:A), Hero name (B2:B), Result (C2:C)

I use D2 To make a unique list of heroes I've played.
=UNIQUE(B2:B)

But then I want to add a corresponding collumn of wins. But I have to manually copy the function below to each row in the D2 and below… Anything to work around that?

=COUNTIFS(B:B;E2;C:C;"Victory")

Best Answer

Short answer

To create an array report, AKA crosstab report, use a Pivot Report.

Instructions

Prepare the data as a simple table.
Select the data
Click Data > Pivot Table...
Add the Name field to the Row section
Add the Result field to the Column section
Add the Name field to the values section. In Summarize by select COUNTA

Example

Assume the following as the data.

+----+--------------+---------+
|    |      A       |    B    |
+----+--------------+---------+
|  1 | Name         | Result  |
|  2 | Batman       | Victory |
|  3 | Superman     | Victory |
|  4 | Batman       | Loss    |
|  5 | Lex Luthor   | Victory |
|  6 | Superman     | Victory |
|  7 | Batman       | Loss    |
|  8 | Lex Luthor   | Victory |
|  9 | Superman     | Victory |
| 10 | Batman       | Loss    |
| 11 | Wonder Woman | Loss    |
+----+--------------+---------+

Result

+---+--------------+------+---------+-------------+
|   |      A       |  B   |    C    |      D      |
+---+--------------+------+---------+-------------+
| 1 |              | Loss | Victory | Grand Total |
| 2 | Batman       | 3    | 1       | 4           |
| 3 | Lex Luthor   |      | 2       | 2           |
| 4 | Superman     |      | 3       | 3           |
| 5 | Wonder Woman | 1    |         | 1           |
| 6 | Grand Total  | 4    | 6       | 10          |
+---+--------------+------+---------+-------------+

Short answer

From the question:

edit: where trying to use an implementation of the script where the result satisfies a condition, I'm not certain but I think the sheet "views" the formula input as giving a result, albeit blank? So not a value of "0"

Instead of something like

values[i][0] > 0

you should use

values[i][0] != ''

Explanation

Part 1: Misconception about blank cells and empty strings.

Your formula doesn't return a blank, it returns a empty string "". A blank cell is coerced to 0 always but a empty string doesn't.

Example:
If A1 has the following formula ="", =ISNUMBER(A1) returns FALSE but =A1+1 returns 1.

By the other hand, on Google Apps Script a blank cell value is handled as an empty string, not as 0 (zero).

Google Apps Script is based on JavaScript no on the "spreadsheet thinking" so there are several things like the default value of a blank cell that are handle in a different way than it's on Google Sheets.

Part 2: One practice for Google Apps Script troubleshooting

In case of doubts, a general practice is to create simple test functions that return the result to the log but in the case of Google Sheets you could use a custom function if you feel more comfortable with the Google Sheets UI than with the Script Editor.

To check the value that Google Apps Script got from an empty string you could use something like the following custom function:

/**
 * Returns a 2D array 
 *
 * @param {"A1"} input Cell reference string
 * @customfunction
 */
function valueDataType(input){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange(input);
  var value = range.getValue();
  var output = [  
     ['Value: ' + value],
     ['Data type: ' + typeof value]
  ];
  return output;
}

Best Answer

Short answer

Instructions

Example

Result

Related Solutions

Google Sheets – Get Unique Rows and Their Count

Google-sheets – Replace formula with result, but exclude formulas that return blanks

Short answer

Explanation

Part 1: Misconception about blank cells and empty strings.

Part 2: One practice for Google Apps Script troubleshooting

Related Topic