Add empty row in an array, each time the content of a given column is different
I try to find a one-formula approach to be able to automatically add an empty row each time the content from one given column changes.
Ideally a one-line formula; I have seen some approached using reduce() tocol() vstack() but could not make it work properly.
In below example, adding an empty row each time the column A value is different:
Start data
| Fruit | Amount |
|---|---|
| Apple | 10 |
| Apple | 20 |
| Pear | 4 |
| Banana | 10 |
| Banana | 5 |
| Cherry | 20 |
Desired output
| Fruit | Amount |
|---|---|
| Apple | 10 |
| Apple | 20 |
| Pear | 4 |
| Banana | 10 |
| Banana | 5 |
| Cherry | 20 |
Many thanks in advance
Solution
You may try:
=reduce(A1:B1,A2:index(A:A,match(,0/(A:A<>""))),lambda(a,c,vstack(a,ifna(if(c<>offset(c,1,),vstack(offset(c,,,,2),),offset(c,,,,2))))))
- google spreadsheet query function column id other than letter of column
- How do I generate OAuth ODBC keys?
- Copy data from one sheet to another, overwriting already entered data
- Autocomplete MaterializeCSS is not working
- If date field is blank return blank but if it has a date return the day in week
- Search for Value and Return All Columns In Which Value Appears
- How to pass a range as a function parameter?
- Google Script pulling cached data
- Select column minus header (or deselect cell)
- How to split array of strings by a delimiter with more than 1 characters
- In sheet one, pull sheet two's cell values by using sheet one's cell values as variables?
- Match Value based on corresponding dates
- Formula to Display Each Multi-Select Value in Google Sheets Dropdown on a Separate Line
- I would like to always have the row above open for new data
- Trouble with Google Apps Script returning a string instead of an integer in Google Sheets
- How to stack multiple queries indirectly defined in an array
- Repeat a function for all selected cells in a range
- Apps Script: eval(include("FilterUICode")) fails with SyntaxError: expected expression, got '<' when injecting JS from separate file
- Google sheets setting columns range in app script
- Reset Column and Row sizes to default
- VLOOKUP IMPORTRANGE to select the column number in another file also
- Displaying hyperlinks to other sheet tabs in a custom sidebar
- Google apps scripts button: can I assign a "global" script that's not attached to the worksheet?
- Date format showing incorrectly in Google sheet from Big Query API
- Formula returns blank while showing result in tooltip
- Deleting all columns if a cell is blank
- Data validation for time input in Google Sheets
- How to hide certain columns?
- How to hide columns across multiple sheets?
- Hide an array of columns in Google App Scripts