Expanding values/counts chart

Title is not very descriptive, sorry about that. Suppose column A contains a list of values (mine in particular are an ordered set of numbers), and column B contains a list of counts for those values. I'm trying to use formulas to have column C fill with each of the values in order, filling as many rows as the counts for that value.

E.g. if
- A1 = Foo
- B1 = 2
- A2 = Bar
- B2 = 3

Then C1:C5 should be {Foo, Foo, Bar, Bar, Bar}

I would suggest a slight change to your layout - insert a blank row at the top, so that your data starts on row 2, and use column C as a helper column so that the output that you are wanting will be in column D (or E).

Put zero in cell C1 and then this formula in C2:

=IF(B2="","",B2+C1)

Copy this down to the bottom of your data, and it will just produce a cumulative sum of the counts for each row. Then you can use this formula in D2 (or E2) to generate your list:

=IF(ROWS($1:1)>MAX(C:C),"",INDEX(A:A,MATCH(ROWS($1:1)-1,C:C)+1))

Copy this down as far as you need it (until you start to get blanks).

Hope this helps.

Pete

Originally Posted by Pete_UK

I would suggest a slight change to your layout - insert a blank row at the top, so that your data starts on row 2, and use column C as a helper column so that the output that you are wanting will be in column D (or E).

Put zero in cell C1 and then this formula in C2:

=IF(B2="","",B2+C1)

Copy this down to the bottom of your data, and it will just produce a cumulative sum of the counts for each row. Then you can use this formula in D2 (or E2) to generate your list:

=IF(ROWS($1:1)>MAX(C:C),"",INDEX(A:A,MATCH(ROWS($1:1)-1,C:C)+1))

Copy this down as far as you need it (until you start to get blanks).

Hope this helps.

Pete

Thanks, that worked perfectly!

That's good to hear - thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Pete