Function name to add rows in Google Sheets

Question

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)?

Answer 1

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).

Answer 2

Short answer

Spreadsheet functions can't insert rows. The alternatives to insert rows are the following methods

1. Manual
2. Scripting
3. Formula building

Proposed method and formulas

In this answer, a formula building method and a set of formulas are proposed. In order to make it easy to read, some parameters were referred by using named ranges. Each of them will be explained a bit later in this answer.

=ARRAY_CONSTRAIN(
    SORT({AuxiliaryArray2;SourceData,AuxiliaryArray1},4,TRUE),
    COUNTA(SourceDataCategory)+COUNTA(CategoryLabels),
    3
)

Explanation

Below is explained the formula building method and the formulas to achieve the desired result.

In order to make easier to build, debug and explain it, instead of nesting several functions that will lead to end with a large formula, the solution was built applying a cascading approach (the sample source data is at the top and each step is built below of its source and the final result is at the end. Once the solution is working, the formulas could be rearranged or refined.

At the top of each step the names of the named ranges are wrote, their reference and formula. This is helpful for explaining others the method followed and as documentation of the solution to make it easy to reuse it later or adapt to another use case.

If you will copy and paste the formulas shown here, please bear in mind that this formulas use comma (,) as the parameter separator. If your spreadsheets use a semicolon (;), consider to first create a new spreadsheet and set the regional settings to a country that use a period (.) as decimal separator, like Mexico or USA among others. After you finish to paste the formulas, you could change the regional settings to one that use a comma (,) as decimal separator if you need that. Google Sheets will update the formulas for you.

Source Data

Two named ranges were defined to reference source data.

     |       A           B           C           D     
==== +  ==========  ==========  ==========  ==========
   1 |  SourceData A2:C6
        SorceDataCategory A2:A6                                   
   2 |  A           18          Atext1                        
   3 |  A            5          Atext2                        
   4 |  C           23          Ctext1                        
   5 |  A           12          Atext3                        
   6 |  B            0          Btest1                        

Category Labels Table

As category labels are not included in the source data, a Category Labels table is built. Two named ranges were defined

     |       A           B           C           D     
==== +  ==========  ==========  ==========  ==========
   7 |  CategoryLabelsTable - A8:B10
        CategoryLabels - B8:B10                                   
   8 |  A           Category A                        
   9 |  B           Category B                        
  10 |  C           Category C                        

Auxiliary Array 1

This array will be used to later add the related category labels to each row of the source data.

     |       A           B           C           D     
==== +  ==========  ==========  ==========  ==========
  11 |  AuxiliaryArray1 A12:A16
        Formula in A12: 
       =ARRAYFORMULA(VLOOKUP(SourceDataCategory,CategoryLabelsTable,2,TRUE))
  12 |  Category A                                    
  13 |  Category A                                    
  14 |  Category C                                    
  15 |  Category A                                    
  16 |  Category B                                    

Auxiliary Array 2

To make easier to visualize, instead of an empty string ("") a minus sign (-) was used.

     |       A           B           C           D     
==== +  ==========  ==========  ==========  ==========
  17 |  AuxiliaryArray2 A18:D20
        Formula in A18
        ={
           CategoryLabels,
           TRANSPOSE(SPLIT(REPT("-,",COUNTA(CategoryLabels)),",")),
           TRANSPOSE(SPLIT(REPT("-,",COUNTA(CategoryLabels)),",")),
           CategoryLabels
        }
  18 |  Category A  -           -           Category A
  19 |  Category B  -           -           Category B
  20 |  Category C  -           -           Category C

Result

Finally, the result.

One row for each category header is added on top of the source data to which was added a column with the related category label.

{AuxiliaryArray2;SourceData,AuxiliaryArray1}

Then it's sorted by the related category label column, and that column is striped.

     |       A           B           C           D     
==== +  ==========  ==========  ==========  ==========
  21 |  Result
        =ARRAY_CONSTRAIN(
           SORT({AuxiliaryArray2;SourceData,AuxiliaryArray1},4,TRUE),
           COUNTA(SourceDataCategory)+COUNTA(CategoryLabels),
           3
        )
  22 |  Category A  -           -                     
  23 |  A           18          Atext1                        
  24 |  A            5          Atext2                        
  25 |  A           12          Atext3                        
  26 |  Category B  -           -                     
  27 |  B            0          Btest1                        
  28 |  Category C  -           -                     
  29 |  C           23          Ctext1                        

Adaptation for use with real source data

Once you get the above method working with the sample data, you have to decide if you need to optimize the number of cells used due to the 2 million cells limitation of Google Sheets.

If not, then you could move each step to another place in your spreadsheet by using cut and paste as Google Sheets will handle the reference update.

If yes, then you should replace some of the named ranges by the intermediate formulas. The resulting formula is:

=ARRAY_CONSTRAIN(
   SORT(
      {
         {
            CategoryLabels,
            TRANSPOSE(SPLIT(REPT("-,",COUNTA(CategoryLabels)),",")),
            TRANSPOSE(SPLIT(REPT("-,",COUNTA(CategoryLabels)),",")),
            CategoryLabels
         };
         SourceData,
         VLOOKUP(SourceDataCategory,CategoryLabelsTable,2,TRUE)
       },
       4,
       TRUE
   ),
   COUNTA(SourceDataCategory)+COUNTA(CategoryLabels),
   3
)

In both cases, you should update named ranges references for the following named ranges or replace them with the actual range references:

• SourceData,
• SourceDataCategory
• CategorLabelsTable
• CategoryLabels

In the part corresponding to the Auxiliary Array, the following part should be added once by each blank column:

TRANSPOSE(SPLIT(REPT("-,",COUNTA(CategoryLabels)),","))

Also the columns indices in the SORT and ARRAY_CONSTRAIN functions, should be updated accordingly.

Note: In some scenarios could be helpful to use whole columns for the source data range, but blank rows should be filtered. For that, assuming that column A will always have a value, then instead of A:C use

=FILTER(A:C,Len(A:A))

References

• File sizes - Google Docs editors Help
• Using arrays in Google Sheets - Google Docs editors Help
• Name a range of cells - Google Docs editors Help