How to make a range repeat n-times from a table in Google SpreadSheet
I am trying to execute the following in google sheets by repeating certain cell values from a range of name and how many it should be iterated , and resulted like this : cells (number of repetitions) and iterating it horizontally till the end of the row.
What i'm trying to do
IF i have a range like this :
Business Communication | 2
Business Intelligence & Data Analytics | 3
Communicative English | 1
Customer Relationship Management | 2
And resulting like this :
Business Communication
Business Communication
Business Intelligence & Data Analytics
Business Intelligence & Data Analytics
Business Intelligence & Data Analytics
Communicative English
Customer Relationship Management
Customer Relationship Management
I've tried some formula, from this thread
But i cant implemented it to my solution, i have thinking about arrayformula, but i'm not really sure that it can resolve my problem, Do you have any idea on how to achieve that using only native formula (no javascript)?
Try
=ARRAY_CONSTRAIN(arrayformula(query(flatten(split(rept("|"&A2:A,B2:B),"|")),"select * where Col1 is not null")),SUM(B2:B),1)
same result with
=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY( REPT(A2:A&"♥", B2:B), ,9^9), "♥"))))
explanation
4 steps
- #1: the core is to concatenate with
=arrayformula(rept("♥"&A2:A5,B2:B5))you will get a list - #2: split that list to obtain a matrix
=arrayformula(split(rept("♥"&A2:A5,B2:B5),"♥")) - #3: to get a linear list, apply flatten
=arrayformula(flatten(arrayformula(split(rept("♥"&A2:A5,B2:B5),"♥")))) - #4: remove blanks, you have 2 ways to do that, first by using a query in wich we will concatenate all the headers
=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY( REPT(A2:A&"♥", B2:B), ,9^9), "♥")))), the second one is more understandable=query(arrayformula(flatten(arrayformula(split(rept("♥"&A2:A5,B2:B5),"♥")))),"select * where Col1 is not null")
references
- Trouble getting log function to work in a custom function for Google sheets
- ranking formula start from negative number
- How can I search a Google Sheet for the cell that matches the intersection of the first column and top row of another sheet
- Javascript case sensitive
- Can I alter a formula using the cell number instead of its cell value?
- Dynamically referencing worksheets to dynamically fill a table based on a condition
- How do you add UI inside cells in a google spreadsheet using app script?
- How can I force data input into cells in Google Sheets to be in a desired date format?
- How do I find a partial match from a data table to an individual cell?
- Highlighting non-first instance duplicates of words and counting highlighted cells
- Lock google sheets for data entry after a wrong entry
- Schedule View: INDEX MATCH from table and return multiple cells into one
- How to Show/Hide rows in google sheets using a script?
- Popping a Modal Dialog when changing the drop down entry in Google Sheets
- Transfer background color from cell range to drop down options
- How can I match a smart chip using SUMIF?
- List Google drive folder contents to google sheets with only new files
- Trying to figure out why my GoogleSheets Date formula is not working
- Count if name matches and outcome is complete
- Google Sheets Row Number of last row in a defined table
- How can I use INDEX and MATCH along with conditions to filter data into different tables?
- Google Sheets formula to remove all punctuation excluding period (.)
- Formula to find missing serial numbers
- Google Sheet Vlookup Multiple Criteria
- Copy Data from one column and past at the end of another column
- Query formula not grouping properly
- Comparing text fields that contain dates in Google Sheets
- Signature Pad (Canvas) to Google Sheets
- How to use the cell number instead of its value in a Filter + Importrange
- regexreplace multiple columns for football data