Repeat the number according to its frequencies

Assuming your data is in A1:B7 (headers in row 1) the simplest way is to use a helper column with this formula in C2 copied down

=SUM(B$1:B1)

and then in E2 copied down use this formula

=IF(ROWS(E$2:E2)>SUM(B:B),"",LOOKUP(ROWS(E$2:E2)-1,C$2:C$7,A$2:A$7))

when you run out of values you get blanks

Hi,

Assuming that the original data table as you give it is in the range A1:B7 (with headers in row 1), enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in your first cell and copy down as required:

=IFERROR(INDEX($A$2:$A$7,MATCH(TRUE,MMULT((--(ROW($A$2:$A$7)>=TRANSPOSE(ROW($A$2:$A$7)))),$B$2:$B$7)>=ROWS($1:1),0)),"")

Regards

Nice formula XOR LX!

another option to do this without a helper column is to use this non-array version

=IF(ROWS(E$2:E2)>SUM(B:B),"",LOOKUP(ROWS(E$2:E2)-1,SUBTOTAL(9,OFFSET(B$1,0,0,ROW(INDIRECT("1:"&COUNT(B:B))))),A$2:A$7))

@daddylonglegs

Thanks! And likewise!

An interesting (and new on me) non-array construction. LOOKUP has some nice and unusual applications - I would invariably use a MATCH(TRUE,SUBTOTAL...etc. array formula if I was going this way, though I shall add this one to my collection now!