Create list including multiples of items from a frequency table

Question

I have a table of products and their quantities. I want to create a list from that table with a row for each product unit. So if my table goes

Product    Quantity
    245           2
    246           0
    247           3
    248           1

I want to create a chart like this:

EDIT: This is what I've tried. Merge3122 is the table. I put the first 8 SKUs in by hand because the maximum quantity is eight so that's all I need to count.

=IF(COUNTIF(F2:F9,F9)<INDEX(Merge3122[Quantity],MATCH(F9,Merge3122[Seller-SKU])),F9,OFFSET(INDEX(Merge3122[Quantity],MATCH(F9,Merge3122[Seller-SKU])),1,0))

Table example image

What code have you tried so far to achieve this result? – FishStix Jun 29 '16 at 01:01 — FishStix, Jun 29 '16 at 01:01

score 1 · Answer 1 · answered Jun 29 '16 at 05:00

With the use of a helper column to keep track of the running total, you can use the following to generate your list. You will need to adjust ranges to suit the needs of your worksheet

First lets assume your first table is in D2 to F6 with row 2 being your header row. In column F we will make a running total starting with:

In F3 use:

=E3

In F4 use:

=E4+F3

Copy the formula from F4 down as far as required.

You have now created a running total of the number of rows of data your are going to need. Now lets generate the list of number using the following:

In H3 use:

=IFERROR(INDEX($D$3:$D$6,IFERROR(MATCH(ROWS($H$3:H3)-1,$F$3:$F$6,1),0)+1),"")

Create list including multiples of items from a frequency table

1 Answers1

Proof of Concept