Expanding a table of frequencies in Excel into a single column

Question

I've read elsewhere on Stack Overflow that Excel questions are acceptable here, so please don't get annoyed :) If they should be elsewhere, just let me know...

I'm frustrated, because I'm pretty sure I used to know how to do this.

Imagine the following table:

Frequency       Object
3               A
2               B
4               C

In a third column, I want Excel to write:

A
A  
A
B
B
C
C
C
C

(3 A's because frequency of A = 3)

I'm pretty sure that this can be done by a single formula copied down the third column, but I can't remember how. Any suggestions?

Answer 1

I liked the elegance of (1) formula, but it will only work if you dont have repeated objects (data).

This will always work, as long you dont have a numeric, diferent from zero, value in E2 Freq values in E3:E6 and Obj in D3:D6, formula starting in P3

=LOOKUP(ROWS(P$3:P3)-1;SUMIF(INDIRECT("E2:E"&ROW($E$2:$E$6));">0");$D$3:$D$6)

or (and, in this case, you can have anything in E2)

=INDEX($D$3:$D$6;IF(ROWS(L$3:L3)<=$E$3;1;1+MATCH(ROWS(L$3:L3)-1;SUMIF(INDIRECT("E3:E"&ROW($E$3:$E$6));">0"))))

Ctrl+Shift+Enter in P3 and copy down

CR