Getting items with required Raw in next Sheet

[pragnesh_50]

Hi,


Sheet1


Product Category-----Raw required in sheet2.

Cat Food---------------2

Dog Food---------------4

Frozen Food------------3

Ambient Food-----------2


I want output in Sheet 2 for Product Category with required raw as per Sheet1:


Sheet2

-Product Category

Cat Food

Cat Food

Dog Food

Dog Food

Dog Food

Dog Food

Frozen Food

Frozen Food

Frozen Food

Ambient Food

Ambient Food


Any Idea?


Regards,

Pragnesh

 

[Jeanbar]

Hi,


I understand you want to count the number of occurrences of the file in Sheet2.

Please consider pivoting this data.


Jeanbar

 

[pragnesh_50]

Thanks..


i want Cells to be filled in next sheet as per number in sheet 1.


if Cat food is with 2 in sheet1, i want A1 & A2 cell of sheet2 filled with Cat food.


if Dog food is 4 times, i want raw A3 to A6 filled with Dog food.


And accordingly....


Regards,

Pragnesh

 

[prasaddn]

Hi Pragnesh,


This should be done easily with some VBA codes..


try this


Sub ExpandProductList()

Dim iLstCell As Integer


Sheet1.Activate

ActiveSheet.Range("A1").Select


iLstCell = Sheet1.Range("A65000").End(xlUp).Row


Dim iRwCntr As Integer

Dim y As Integer

iRwCntr = 1


Sheet2.Activate

For i = 1 To iLstCell

If Sheet1.Range("B" & i).Value > 0 Then

For y = 1 To Sheet1.Range("B" & i).Value

Sheet2.Range("A" & iRwCntr).Value = Sheet1.Range("A" & i).Value

iRwCntr = iRwCntr + 1

Next

End If


Next


MsgBox "Done"


End Sub


You can assign this code to button or short cut as well.


Regards,

Prasad

 

[pragnesh_50]

Hi Prasad,


Thanks.


I got compile error: variable not defined and it For------ i = 1 To iLstCell ----where fist i is highlighted.


Regards,

Pragnesh

 

[pragnesh_50]

Hi Prasad,


I have added "Dim i As Long" to third line in your given macro.


And it is working for me. Kindly ignore my last comment.


Thanks for your great help!


Regards,

Pragnesh

 

[srirajsa]

really great prasad garu thanks it helped me lot

 

[prasaddn]

Very nice comments Srirajsa-Garu