Repeat each row N times in Google Sheets
I have a column containing 3 rows and I want to be able to repeat those rows 5 times each.
Example
| Name |
|---|
| Dog |
| Cat |
| Ball |
Desired Output
| Output |
|---|
| Dog |
| Dog |
| Dog |
| Dog |
| Dog |
| Cat |
| Cat |
| Cat |
| Cat |
| Cat |
| Ball |
| Ball |
| Ball |
| Ball |
| Ball |
Here's what I have tried:
=TRANSPOSE(split(rept(join(";",A:A)&";",5),";"))
My attempt produces:
| Output |
|---|
| Dog |
| Cat |
| Ball |
| Dog |
| Cat |
| Ball |
| Dog |
| Cat |
| Ball |
| Dog |
| Cat |
| Ball |
| Dog |
| Cat |
| Ball |
Solution
=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(
REPT(A1:A3&"♠", 5), ,999^99), "♠"))))
=SORT(TRIM(TRANSPOSE(SPLIT(QUERY(ARRAYFORMULA(
REPT(A1:A3&"♠", 5)), ,999^99), "♠"))), 1, 0)
- Google Sheets Conditional formatting custom formula: if current cell is empty, but other cell is not
- Tabletop.js can't see Google Spreadsheet data
- How do you find and replace characters with app scripts?
- Harvest data from Google Spreadsheet?
- SUMIFS with OR criteria in Google Sheets
- Parse error from using .setValue or .SetFormula to set a formula containing a query function that has multiple single and double quotes
- Formula parse error after “setformula” in google sheets
- Multiple COUNTIFS inside IFS formula (FORMULA PARSE ERROR)
- Google Application Script QUERY "Formula Parse Error"
- setFormula statement producing Parse Error
- setFormula parenthesis or quotes missing?
- Getting parsing error setFormula Google Script
- SUM above x Cells based on value in Reference Cell
- Google Apps Script Change nth Row height in Sheets
- Google Sheets Apps Script "Working" does not disappear
- Forbidden: 403 Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials
- Getting Google Spreadsheet CSV into A Pandas Dataframe
- Printing from a range
- How to get data from Google Sheet to use in deployed web app
- Google spreadsheet error when using SpreadsheetApp.getUi() function
- SpreadsheetApp.getUi() operation not supported?
- Google Sheets QUERY using SQL to compare two date columns across sheets
- Google Apps Script how to get "live" data?
- Convert a fraction to a decimal in Google Sheets
- How to Submit an HTML Form to Google Sheets…without Google Forms
- Appending multiple rows to Spreadsheet - Google Apps Script
- Make Button to increase all dates in Column by 1 Google Sheets Apps Script
- Tic Tac Toe in Google Sheets (Apps Script)
- Google API Add row at position Google Spreadsheet PHP
- Google Apps Script Multiple Find and replace regex in Google Sheets