Arrayformula Looping Repetition in Google Sheets

user_1694426 0
Viewed 334

For example I have this set of data below:

Header 1    Header 2
Mouse       5
Elephant    4

What I want to happen is for the word "Mouse" to repeat 5 times and the word "Elephant" to repeat 4 times from top to bottom in Column C.

The output will look something like this:

Header 1    Header 2    Header 3
Mouse       5           Mouse
Elephant    4           Mouse
                        Mouse
                        Mouse
                        Mouse
                        Elephant
                        Elephant
                        Elephant
                        Elephant

How to do this in Google Sheets?

Thank You

google-sheets
2 Answers

Supposing that your "Header 1" is in A1 and the rest of your posted sample is in A:B, place the following formula in C1 of an otherwise empty Column C:

=ArrayFormula({"Header 3"; QUERY(FLATTEN(SPLIT(FILTER(REPT(A2:A&"~",B2:B),A2:A<>""),"~",1,1)),"Select * WHERE Col1 Is Not Null")})

This formula will produce the header text (which you can change within the formula as you like) and all results.

REPT will repeat anything in A2:A (with a tilde appended to the end) B2:B times to form virtual single-cell strings.

FILTER will make sure only occupied rows are included in the REPT function.

SPLIT will split those repetitive strings into separate horizontal cells at those intermittent tildes.

FLATTEN will take all of the results of the SPLIT and form one column from them (spaces and all, since the number of cells per virtual row will differ depending on the requested number of repetitions).

QUERY will purge any empty rows that would have otherwise appeared in the FLATTEN results.

ArrayFormula is necessary because the formula is processing a range as opposed to a single row's data.

user_139032830

Try the below formula:

Assuming your data range is A2:B and you are entering the formula in C2, if not then you can change the range accordingly

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