Formula to change Qty value to list of data

[jrocco]

I receive data from a customer such as in file "Applied Data.xls"
Column A is Quantity. Column B is data.

I need to come up with a list of data with individual lines for duplicates rather than a Quantity Column. For example Qty 15 of Data 9929 in first line of file must show "9929" listed 15 times in Column A.

2nd attachment shows the file I need with example data & quantities. Cells A1, "Applied1" and A2 "txtField1" must remain UNCHANGED (but could be added after manually if needed).
Can a formula be written to take data from "Applied Data.xls" and create a new file (or tab) looking like "Applied1.csv" ?

It must work with any number of records. For example, there may be dozens of data records with multiple Qtys required of each.
Open to suggestions. Thanks!

[Crooza]

Use this macro to put the data in column D (you could put it in another Tab if you wished but its easier to see in column D)

Please Login or Register  to view this content.

[Ace_XL]

If you're looking for a formula solution, with your data in Column A and Column B (as in your sample file), use this this array formula in a separate column

=IF(ROW($A1)>SUM($A:$A),"",LOOKUP(ROW($A1)-1,MMULT(IF(ROW($A$1:$A$3)-1>=TRANSPOSE(ROW($A$1:$A$3))=TRUE,1,0),$A$1:$A$3),$B$1:$B$3))

Copy down as far as you like

Adjust ranges and references for your actual data

[jrocco]

Hi Ace,
Thanks for your help, but I tried the formula in C1 and i'm getting a #VALUE error?

[Ace_XL]

I should've mentioned, the formula needs to be array entered

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.