2

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.

2

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:

2
  • Hey @marikamitsos, thank you again for your quick response! It works like magic! :) :) – Genny Dec 21 '20 at 12:24
  • 1
    Season's greetings. Glad I worked for you. :) – marikamitsos Dec 23 '20 at 20:17
2

Here is another approach:

=ArrayFormula(VLOOKUP(TRANSPOSE(SPLIT(JOIN("",REPT(ROW(A:A)&"@",A:A)),"@",0,1)),{ROW(A:A),B:B},2,FALSE))

How It Works

=ArrayFormula(...) signifies that the formula will process an entire range and not just one cell.

Now, working from the inside out...

REPT(ROW(A:A)&"@",A:A) will add the @ symbol onto the end of each row number and then repeat that string however many times is listed in A:A. For instance, if row 1 Column A indicates to repeat 1x, the string for that row will be 1@. If row 2 Column A indicates it is to repeat 2x, the sting for that row will be 2@2@.

JOIN("",...)

All of those strings will be joined into one long string (e.g., 1@2@2@3@3@3@...)

SPLIT(...,"@",0,1)

That long string will be split apart at the @ symbol into a long virtual row of individual numbers, e.g.:

1 2 2 3 3 3...

TRANSPOSE(...)

That long virtual row of individual numbers will be transposed into a column of the same numbers, e.g.:

1

2

2

3

3

3

etc.

VLOOKUP(...,{ROW(A:A),B:B},2,FALSE))

Each of those individual numbers in the column will be looked up within a virtual array composed of all row numbers in column one and all B:B text in column two. When each exact match is found (signified by FALSE), the Column 2 data will be returned.

2
  • Hey Erik, thank you for your answer too. It works like magic :D. & best of all you've broken down the steps. It helps understand this really well. – Genny Dec 21 '20 at 12:24
  • 1
    Happy to have helped broaden your Sheets knowledge, Genny. If everyone helps someone just a little, in whatever ways we can, the world can't help but be a better place. – Erik Tyler Dec 21 '20 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.