Google Sheets – Function Name to Add Rows

google sheetsworksheet-function

Is it possible to add rows with a built-in function (such as MIN() or INDIRECT()) in Google Sheets?

I would like to fill rows in the main sheet such as 

+---+----+--------+
| A | 18 | Atext1 |
| A |  5 | Atext2 |
| C | 23 | Ctext1 |
| A | 12 | Atext3 |
| B |  0 | Btest1 |
+---+----+--------+

Then, I would like the spreadsheet to use the "main sheet" so that I see in another sheet:

+------------+----+--------+
| Category A |    |        |
| A          | 18 | Atext1 |
| A          |  5 | Atext2 |
| A          | 12 | Atext3 |
| Category B |    |        |
| B          |  0 | Btest1 |
| Category C |    |        |
| C          | 23 | Ctext1 |
+------------+----+--------+

The only difficulty I have is to dynamically insert lines depending on how many As or Bs are in the main sheet.

Is there a function to do this easily (= without writing a script—I'd prefer to avoid going into this)?

Best Answer

Inserting rows

No, spreadsheet functions cannot add or remove rows or columns. The two ways to perform these operations are: manually by the user, or by a script.

Alternative solution

That said, you can create an output of the desired kind with existing spreadsheet functions, without inserting new rows. My approach uses a helper column that can be subsequently hidden.

Cell Z1 of another sheet: =sort(unique(filter(main!A:A,len(main!A:A))))

This creates a sorted list of categories present in data. Column Z is a helper column and can be hidden.

Cell A1 of another sheet: 

=array_constrain(sort({filter({"Category "&Z:Z, iferror(Y:Z/0), Z:Z}, len(Z:Z)); filter({main!A:C, main!A:A&" "}, len(main!A:A))}, 4, true), 1e6,3)

Explanation

The formula in A1 does several things:

filter({"Category "&Z:Z, iferror(Y:Z/0), Z:Z}, len(Z:Z)) creates a table where iferror is used to produce blank cells. It does not matter which columns are used in iferror (as long as there is no cyclic dependence), but the number must be one less than the number of columns in the input data. The result looks like this:

+------------+--+--+---+
| Category A |  |  | A |
| Category B |  |  | B |
| Category C |  |  | C |
+------------+--+--+---+

filter({main!A:C, main!A:A&" "}, len(main!A:A)) appends the original table, where the first column is repeated again as 4th, but with a space added to it. This 4th column is used for sorting only. 

+------------+----+--------+----+
| Category A |    |        | A  |
| Category B |    |        | B  |
| Category C |    |        | C  |
| A          | 18 | Atext1 | A  |
| A          |  5 | Atext2 | A  |
| C          | 23 | Ctext1 | C  |
| A          | 12 | Atext3 | A  |
| B          |  0 | Btest1 | B  |
+------------+----+--------+----+

Finally, sort(..., 4, true) sorts by the 4th column. The added space makes sure that items appear after categories.

The 4th column is then suppressed by array_constrain.

+------------+----+--------+
| Category A |    |        |
| A          | 18 | Atext1 |
| A          |  5 | Atext2 |
| A          | 12 | Atext3 |
| Category B |    |        |
| B          |  0 | Btest1 |
| Category C |    |        |
| C          | 23 | Ctext1 |
+------------+----+--------+

Notation

If your locale uses commas as decimal separators, replace commas by semicolons in the list of arguments. But within the arrays {}, commas should be replaced by backslashes \. (Source: using arrays).

Related Topic