Saturday, May 26, 2012

yogi_Expand An Item Count Into Corresponding Number Of Rows

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #563    May 26, 2012

user fearless said:
expand an item count into a number of rows
[Using Chrome/OS X, but that's probably not relevant.]
Consider the following on one sheet:
quantity, description, accumulated
0, pony, 0
4, lollipop, 4
2, rainbow, 6
1, fairy, 7
On another sheet, I'd like that to expand to be:
item, description
1, lollipop
2, lollipop
3, lollipop
4, lollipop
5, rainbow
6, rainbow
7, fairy
that is: I get <quantity> rows of each item.  I can do this with a nested IF statement, but there's a nested IF for each item, so it gets unwieldy very quickly.  I'd prefer NOT to resort to scripting.  VLOOKUP holds promise, but I haven't been able to make it jump through this particular hoop.
Any suggestions?
following is a solution to the problem ... for clarity I have posted the results in the same sheet ... posting the results in another sheet is not a problem