0

Here's an simplified example of what I am trying to do.

cat  2
dog  3

Translated to:

cat
cat
dog
dog
dog

Is that possible with just a function in a cell or do I have to use something like a macro? How would I do that?

kschieck
  • 1,407
  • 2
  • 16
  • 29

3 Answers3

2

In Google sheets, assuming dog and cat are in A1 and A2 and 2 and 3 are in B1 and B2, this will do it:

=transpose(split(rept(A1&" ",B1)&rept(A2&" ",B2)," "))
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
1

In Google sheets, assuming "dog 2" and "cat 3" are in A1 and A2, this will do it:

=transpose(QUERY({(split(rept(split(A1," ")&" ",query(split(A1," "), "SELECT Col2"))," ")),(split(rept(split(A2," ")&" ",query(split(A2," "), "SELECT Col2"))," "))},,))

Credit:

G4mo in StackOverflow How to make a range repeat n-times in Google SpreadSheet

Tedinoz
  • 5,911
  • 3
  • 25
  • 35
  • This did work, though it is a bit more complicated than the other one which I marked as the answer. – kschieck Oct 09 '18 at 10:57
  • @kschiek Point noted. It is a cumbersome formula and could be improved upon. But FWIW it also assumes a different starting position, where it is necessary to first split the text and number, and then to generate the list. EdNelson's answer is a thing of beauty and a joy to behold, and It may be useful for others at some future date to have both options. – Tedinoz Oct 09 '18 at 18:42
1

In Excel, you can use either VLOOKUP() or a combination of INDEX() and MATCH().

With data in cols A and B, in C1 enter 1 and in C2 enter:

=C1+B1

and copy downward. In D1 enter:

=IF(ROWS($1:1)>SUM(B:B),"",INDEX(A:A,MATCH(ROWS($1:1),C:C,1)))

and copy downwards:

enter image description here

The helper column D translates the repetition factor in column B into match index.

(Placing a helper column to the left of the data would allow the use of VLOOKUP())

Gary's Student
  • 95,722
  • 10
  • 59
  • 99