Formula to Copy/Paste data based on value of another cell, skipping zeros value & blanks

Hi,

I spent the whole day to figure out a formula that does exactly one thing: copy (from sheet1) and paste data (to sheet2) a number of times depending on a value in an adjacent column. I searched several forum site and stumbled upon a VBA codes but I prefer a formula.

For instance:

Sheet1

Product Code(A)	Product Name(B)	QTY(C)	Price(D)
02362	USB Drive	3	$1.75
03516	HDD	2	$23.50
06251	HDD Enclosure	0	$25.25
07542	LCD Monitor	2	$125.25
09561	Asus Netbook	0	$150

In Sheet2, in columns A and B only, the following show be the results:

Product Code (A)	Product Name (B)
02362	USB Drive
02362	USB Drive
02362	USB Drive
03516	HDD
03516	HDD
07542	LCD Monitor
07542	LCD Monitor

Can you please help me for the formula in column A and B in sheet 2 to get the desired results above?

Regards,
kolokoy

Hi hemesh,

Thank you for the quick reply. It is indeed a very complex formula to me. However, the result only copied the data twice. Here is a screen shot:
Untitled.png

It appears that the problem is only in the first row, it is always 1 less than the copied data. For instance, I tried to change Sheet1 A2 to 5, it only copied the data 4 times. While the rows below it are correct.

Thank you very much.

Regards,
kolokoy

Hi hemesh,

Thank you very much. I tried the approach with the helper column. However, for the first row, USB Drive with QTY 3, the data were copied two times only and another one at the bottom of the list. So it is 3 in total, which is right, but is there a way to list the 3 USB drives like the other rows?

See screen shot:
Untitled2.png

Thank you very much for your help!

Regards,
kolokoy

Thank you hemesh for a very quick solution! It is exactly doing what I described now! Thank you very much! I will now mark this SOLVED.

Regards,
kolokoy