Reversed of Frequency function

  • Thread starter Thread starter mrbenit
  • Start date Start date
M

mrbenit

Hello,

I need to recreate on original dataset of values from a frequency table, i.e

x f
1 2
2 1
3 4
4 1

converted to

1 1 2 3 3 3 3 4

Any sugestions?

Thanks in advance
 
Hi

Have a look at the REPT function:
=REPT(A2,B2)&REPT(A3,B3)&REPT(A4,B4)&REPT(A5,B5)
will put the result in a single cell.


Andy.
 
Hello

Assuming data in A2:B10 (no blanks. x in A2:A10 and f in B2:B10)
this array formula seems to fit the bill.

(There has to be a cell above the starting cell (here C1), and that cell
must not contain a number!)
In C2:

=IF(ROW()-ROW($C$2)>=SUM($B$2:$B$10),"No more!", INDEX($A$2:$A$10,
MATCH(MIN(IF(COUNT($C$1:C1)<MMULT((ROW($B$2:$B$10)>=
TRANSPOSE(ROW($B$2:$B$10)))+0,$B$2:$B$10),MMULT((ROW($B$2:$B$10)>=
TRANSPOSE(ROW($B$2:$B$10)))+0,$B$2:$B$10))),MMULT((ROW($B$2:$B$10)>=
TRANSPOSE(ROW($B$2:$B$10)))+0,$B$2:$B$10),0)))

Enter the formula with <Shift><Ctrl><Enter>, also if edited later. If done
correctly Excel will display the formula in the formula bar enclosed in
curly brackets { }. Don't enter these brackets yourself.

Copy C2 down as far as necessary (at least the number of cells, matching
the sum of the numbers in B2:B10) with the fill handle (the little square in
the lower right corner of the cell).

C2:C?? will display the original dataset
 
Back
Top