Help w/ VB to match & calculate
 

hello,

I have the following data:
Wrkctr Mach Setup Run
MCB607 5 30
XJ2607 5 10
QPU607 5 2
XM4607 15 5
XM4607 2964 815 15
XM4607 10 5
XM4607 15 5
XM4607 10 5
XM4607 15 5
XM4607 2964 750 15
XM4607 2964 100 15
XM4607 5 0
XM4607 10 5
XM8607 9435 5 60
XJ2607 15 120
XJ2607 15 10
QPU607 5 2
XM6607 4955 90 600
XM8607 9435 15 30
XM9607 0 240
XM9607 5 74
XN8607 9436 23 10
XN2607 PA111 30 1
QP5607 4 24
XJ2607 7 1200
QPU607 5 45
QPU607 5 10
QPU607 10 60
QPX607 5 45

I need to create a macro to give me this results:
Wrkctr Setup Run Cycle time
MCB607 5 30 35
QP5607 4 24 28
QPU607 30 119 149
QPX607 5 45 50
XJ2607 42 1340.2 1382.2
XM4607 1745 75 1820
XM6607 90 600 690
XM8607 20 90 110
XM9607 5 314 319
XN2607 30 1 31
XN8607 23 10 33

Calculate the sum of setup and run time for each wrkctr. The cycle time is
sum of setup & run time. number of rows of data vary. Thanks for any help.

Help w/ VB to match & calculate
 

Sub AddThemUp()
Sheets(1).Select
Columns("A").Copy
Sheets(2).Range("A1").Pastespecial
Columns("C:D").Copy
Sheets(2).Range("B1").PasteSpecial

Dim rng as Range

With Cells
Set rng = .Range(.Cells(1, 3), .Cells(1, 3).End(xlDown))
rng.Select
End With

Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
Cells(RowNdx - 1, ColNum + 1) = Cells(RowNdx - 1, ColNum + 1) +
Cells(RowNdx, ColNum + 1)
Cells(RowNdx - 1, ColNum + 2) = Cells(RowNdx - 1, ColNum + 2) +
Cells(RowNdx, ColNum + 2)
Cells.EntireRow(RowNdx).Delete shift:=xlUp
End If
Next RowNdx
End sub
--
Best wishes,

Jim


"Cam" wrote:

hello,

I have the following data:
Wrkctr Mach Setup Run
MCB607 5 30
XJ2607 5 10
QPU607 5 2
XM4607 15 5
XM4607 2964 815 15
XM4607 10 5
XM4607 15 5
XM4607 10 5
XM4607 15 5
XM4607 2964 750 15
XM4607 2964 100 15
XM4607 5 0
XM4607 10 5
XM8607 9435 5 60
XJ2607 15 120
XJ2607 15 10
QPU607 5 2
XM6607 4955 90 600
XM8607 9435 15 30
XM9607 0 240
XM9607 5 74
XN8607 9436 23 10
XN2607 PA111 30 1
QP5607 4 24
XJ2607 7 1200
QPU607 5 45
QPU607 5 10
QPU607 10 60
QPX607 5 45

I need to create a macro to give me this results:
Wrkctr Setup Run Cycle time
MCB607 5 30 35
QP5607 4 24 28
QPU607 30 119 149
QPX607 5 45 50
XJ2607 42 1340.2 1382.2
XM4607 1745 75 1820
XM6607 90 600 690
XM8607 20 90 110
XM9607 5 314 319
XN2607 30 1 31
XN8607 23 10 33

Calculate the sum of setup and run time for each wrkctr. The cycle time is
sum of setup & run time. number of rows of data vary. Thanks for any help.

Help w/ VB to match & calculate
 

You can do this with a pivot table. (under the data menu)

After you have the table, create a calculated field for Cycle time.

Debra Dalgleish has all kinds of information on pivot tables at her site.

http://www.contextures.com. Select Tech tips and then look at the Index.
--
Regards,
Tom Ogilvy

"Cam" wrote:

hello,

I have the following data:
Wrkctr Mach Setup Run
MCB607 5 30
XJ2607 5 10
QPU607 5 2
XM4607 15 5
XM4607 2964 815 15
XM4607 10 5
XM4607 15 5
XM4607 10 5
XM4607 15 5
XM4607 2964 750 15
XM4607 2964 100 15
XM4607 5 0
XM4607 10 5
XM8607 9435 5 60
XJ2607 15 120
XJ2607 15 10
QPU607 5 2
XM6607 4955 90 600
XM8607 9435 15 30
XM9607 0 240
XM9607 5 74
XN8607 9436 23 10
XN2607 PA111 30 1
QP5607 4 24
XJ2607 7 1200
QPU607 5 45
QPU607 5 10
QPU607 10 60
QPX607 5 45

I need to create a macro to give me this results:
Wrkctr Setup Run Cycle time
MCB607 5 30 35
QP5607 4 24 28
QPU607 30 119 149
QPX607 5 45 50
XJ2607 42 1340.2 1382.2
XM4607 1745 75 1820
XM6607 90 600 690
XM8607 20 90 110
XM9607 5 314 319
XN2607 30 1 31
XN8607 23 10 33

Calculate the sum of setup and run time for each wrkctr. The cycle time is
sum of setup & run time. number of rows of data vary. Thanks for any help.