Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to multiply rows to match the specific value in the row
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to multiply rows to match the specific value in the row
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 "Paatu" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to multiply rows to match the specific value in the row
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 "Bernie Deitrick" wrote: 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 "Paatu" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to multiply rows to match the specific value in the row
See
http://www.mvps.org/dmcritchie/excel/getstarted.htm In article , Paatu wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MULTIPLY CELLS IN ROWS CONTINUALLY | New Users to Excel | |||
What function will find and multiply specific values in excel? | Excel Worksheet Functions | |||
What function will find and multiply specific values in excel? | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
Rank - Specific match only | Excel Worksheet Functions |