Excel Catalog item list parts qanitty problem

[VicV]

I'm not sure how to ask the appropriate question to solve my problem.
One of my employees enter catalog numbers and quantities in an Excel
work sheet. It's a very big list and we needed the format to be one each
of the catalog number per line. What this person did was count the
number of units and put the catalog number and then put quantity on the
same line. I guess the best way to explain would be with an example.

What they did

Catalog No. Description quantity
2021 blue widget 3
2022 red widget 2
2023 green widget 1

What we actually need

Catalog No. Description quantity
2021 blue widget 1
2021 blue widget 1
2021 blue widget 1
2022 red widget 1
2022 red widget 1
2023 green widget 1

Is there a way with a formula or Macro to take the existing list and
change it to the desired result?

Thank You for any suggestions in advance.
VicV

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Dave Peterson]

Your data is in A:C with headers in row 1?

if yes, then maybe this macro will do what you want:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim NumRowsToRepeat As Long
Dim curWks As Worksheet
Dim newWks As Worksheet

Set curWks = ActiveSheet
Set newWks = Worksheets.Add
newWks.Range("A1").Resize(1, 3).Value _
= Array("Catalog No.", "Description", "Quantity")

oRow = 2
With curWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow

NumRowsToRepeat = .Cells(iRow, "C").Value

newWks.Cells(oRow, "A").Resize(NumRowsToRepeat, 1).Value _
= .Cells(iRow, "A").Value

newWks.Cells(oRow, "B").Resize(NumRowsToRepeat, 1).Value _
= .Cells(iRow, "B").Value

newWks.Cells(oRow, "C").Resize(NumRowsToRepeat, 1).Value = 1

oRow = oRow + NumRowsToRepeat
Next iRow
End With

newWks.UsedRange.Columns.AutoFit
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm