How to create a range of numbers based on quantity: I'm desperate

[Philosopher]

I have a dataset containing the age (a) and the number (#) of people with that age in one column each. The set looks like this:

a | #
1 | 2
2 | 3
3 | 0
4 | 1

Now, there are all sorts of inelegant ways to transform the above table into a list where the respective age appears #-times, like so:

1 (=A1)
1
2
2
2
4 (=A6)

I don't have space for such a solution. Instead, I'm looking for a function, which directly applies to the two-column-table above, and which gives me a value that is equivalent to the range A1:A6 from the list. Such a function would make my life much easier, not least of all because I'd finally be able to avoid this stupid SUMPRODUCT function to calculate the average ages.

[hrlngrv]

If you're actually using Office 365, you should be able to use spilled formulas. If the a|# data were in E1:F4, and if everything in E1:E4 were a single character,

A1:

Please Login or Register  to view this content.

If the values in column E could be varying numbers of characters, it's more complicated.

A1:

Please Login or Register  to view this content.

[Philosopher]

FANTASTIC!

The second solution is exactly what I was looking for and there was absolutely now way that I would have figured it out myself. Many thanks!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.