How to multiply rows to match the specific value in the row


G

Guest

This simple example will clarify my problem:

title color weight amount
product1 yellow 12 3
product2 red 11 1
product3 green 6 2
product4 purple 14 4


title color weight amount
product1 yellow 12 1
product1 yellow 12 1
product1 yellow 12 1
product2 red 11 1
product3 green 6 1
product3 green 6 1
product4 purple 14 1
product4 purple 14 1
product4 purple 14 1
product4 purple 14 1

So the number of rows of each product match the value "amount" of each
product.
So if there are 3 yellow products, I have to make 3 rows of yellow product.

I'm using excel2003 but I'm also able to do this "row multiplication" with
2007 if it helps.

Thanks!
 
Ad

Advertisements

B

Bernie Deitrick

Paatu,

A macro will do it easily. This example assumes that your data table is in columns A to D, starting
at row 1.

Sub TryNow()
Dim myRow As Long
Dim myCol As Integer

myCol = 4

On Error Resume Next

For myRow = Cells(Rows.Count, myCol).End(xlUp).Row To 2 Step -1
Cells(myRow, 1).EntireRow.Copy
Cells(myRow, 1).EntireRow.Resize(Cells(myRow, myCol).Value - 1).Insert
Cells(myRow, myCol).Resize(Cells(myRow, myCol).Value).Value = 1
Next myRow

End Sub

HTH,
Bernie
MS Excel MVP
 
G

Guest

Thank u Bernie,

So how do I use that macro below?
I'm kinda rookie at excelling so how do U make a macro?
Step by step, if possible.

thanks
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top