How to make a range repeat n-times from a table in Google SpreadSheet

Question

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

This can be done with an array formula. However, your screenshot image doesn't show the range where the data is located. It would be simpler to help you if you share a link to the spreadsheet (or to a copy of the spreadsheet). — Erik Tyler, Jan 18 '22 at 17:22

score 0 · Answer 1 · answered Jan 18 '22 at 18:56

0

You can try the below formula:

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(
 REPT(A2:A&",", B2:B), ,999^99), ","))))

answered Jan 18 '22 at 18:56

Rocky

950
1
7
12

Can you explain every formula used to solve my problem? and why it must be used, what effect does it have on the result. Thanks ! – Gregorius Alvin Jan 19 '22 at 02:07

Mike Steelson · Accepted Answer · 2022-01-19T05:08:41.060

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

Can you explain every formula used to solve my problem? and why it must be used, what effect does it have on the result. Thanks ! — Gregorius Alvin, Jan 19 '22 at 02:07

How to make a range repeat n-times from a table in Google SpreadSheet

2 Answers2

explanation

references