Google Sheets – Copy/Multiply Cell Contents Based on Another Cell

copy/pasteformulasgooglegoogle sheetsgoogle-sheets-arrayformula

I have a list of items, Col. B & numbers in Col. A.
I want to multiply/copy the text in cell B based on the number in their relative cell A. I.e. multiply 'B' by the number of times in 'A'

Here's the sample spreadsheet:https://docs.google.com/spreadsheets/d/1_jbgugr93t6CYY08Bi83SH3pWJIH8B1ukfCKiPwtt4M/edit?usp=sharing

I've also shown in Col. D in red, what I envision as my result would look like. Right now I do this manually, copy the contents the relevant number of times.

Is there a way to do this faster.

Best Answer

Please use the following

=QUERY(ArrayFormula(flatten(IFERROR(SPLIT(REPT(B1:B&"@",A1:A),"@")))),"where Col1<>'' ")

enter image description here

How the (undocumented) flatten function work.
When we use flatten on an array of cells, it transposes the array row by row into a single column.

Please notice the difference between flatten and TRANSPOSE

+------+-----+----+-----------------+----------+----------+
| array of cells  | =flatten(A1:C2) |  =TRANSPOSE(A1:C2)  |
+------+-----+----+-----------------+----------+----------+
|   1  |  2  |  3 |        1        |     1    |     4    |
|   4  |  5  |  6 |        2        |     2    |     5    |
|      |     |    |        3        |     3    |     6    |
|      |     |    |        4        |          |          |
|      |     |    |        5        |          |          |
|      |     |    |        6        |          |          |
+------+-----+----+-----------------+----------+----------+

Functions used:

Related Topic