Hi,

I have an Excel file with the following info. I need to calculate the
cycletime (setup + run time) for each Workceter.
What function I need to lookup at column A, put a workcenter (only once if
multiple) in a cell, then calculate the total cycletime for each workcenter.
Thanks

DATA:
A B C D
1 Workcenter Machine Setup Run time
2 MCB607 5 30
3 XJ2607 5 10
4 QPU607 5 2
5 XM4607 15 5
6 XM4607 296 815 15
7 XM4607 296 5 0
8 XM4607 296 110 15
9 XM4607 5 0
10 XM4607 10 5
11 XM4607 15 5
12 XM4607 296 750 15
13 XM4607 296 100 15
14 XM4607 5 0
15 XM4607 10 5
16 XM8607 943 5 60
17 XJ2607 15 120
18 XJ2607 15 10
19 QPU607 5 2
10 XM6607 495 90 600

Result:
Workcenter Cycle time
MCB607 35
XJ2607 15
QPU607 7
XM4607 1920
XM8607 65
XJ2607 160
QPU607 7
XM6607 690

VBA solution.

Assumes input on Sheet1, output on Sheet2

Sub Sumwkctr()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim r As Long, lastrow As Long, orow As Long
Dim sumtime As Double

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

orow = 1
ws2.Cells(orow, 1).Resize(1, 2) = Array("WorkCentre", "Cycle Time")

With ws1

lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
wkctr = .Cells(2, 1)
sumtime = 0
For r = 2 To lastrow
If .Cells(r, 1) < wkctr Then
orow = orow + 1
ws2.Cells(orow, 1) = wkctr
ws2.Cells(orow, 2) = sumtime

wkctr = .Cells(r, 1)
sumtime = 0

End If
sumtime = sumtime + .Cells(r, 3) + .Cells(r, 4)
Next r

orow = orow + 1
ws2.Cells(orow, 1) = wkctr
ws2.Cells(orow, 2) = sumtime
End With


End Sub


"Cam" wrote:

Hi,

I have an Excel file with the following info. I need to calculate the
cycletime (setup + run time) for each Workceter.
What function I need to lookup at column A, put a workcenter (only once if
multiple) in a cell, then calculate the total cycletime for each workcenter.
Thanks

DATA:
A B C D
1 Workcenter Machine Setup Run time
2 MCB607 5 30
3 XJ2607 5 10
4 QPU607 5 2
5 XM4607 15 5
6 XM4607 296 815 15
7 XM4607 296 5 0
8 XM4607 296 110 15
9 XM4607 5 0
10 XM4607 10 5
11 XM4607 15 5
12 XM4607 296 750 15
13 XM4607 296 100 15
14 XM4607 5 0
15 XM4607 10 5
16 XM8607 943 5 60
17 XJ2607 15 120
18 XJ2607 15 10
19 QPU607 5 2
10 XM6607 495 90 600

Result:
Workcenter Cycle time
MCB607 35
XJ2607 15
QPU607 7
XM4607 1920
XM8607 65
XJ2607 160
QPU607 7
XM6607 690

Thanks Toppers,

Everything worked except, it lists a duplicate workcenter multiple times.

"Toppers" wrote:

VBA solution.

Assumes input on Sheet1, output on Sheet2

Sub Sumwkctr()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim r As Long, lastrow As Long, orow As Long
Dim sumtime As Double

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

orow = 1
ws2.Cells(orow, 1).Resize(1, 2) = Array("WorkCentre", "Cycle Time")

With ws1

lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
wkctr = .Cells(2, 1)
sumtime = 0
For r = 2 To lastrow
If .Cells(r, 1) < wkctr Then
orow = orow + 1
ws2.Cells(orow, 1) = wkctr
ws2.Cells(orow, 2) = sumtime

wkctr = .Cells(r, 1)
sumtime = 0

End If
sumtime = sumtime + .Cells(r, 3) + .Cells(r, 4)
Next r

orow = orow + 1
ws2.Cells(orow, 1) = wkctr
ws2.Cells(orow, 2) = sumtime
End With


End Sub


"Cam" wrote:

Hi,

I have an Excel file with the following info. I need to calculate the
cycletime (setup + run time) for each Workceter.
What function I need to lookup at column A, put a workcenter (only once if
multiple) in a cell, then calculate the total cycletime for each workcenter.
Thanks

DATA:
A B C D
1 Workcenter Machine Setup Run time
2 MCB607 5 30
3 XJ2607 5 10
4 QPU607 5 2
5 XM4607 15 5
6 XM4607 296 815 15
7 XM4607 296 5 0
8 XM4607 296 110 15
9 XM4607 5 0
10 XM4607 10 5
11 XM4607 15 5
12 XM4607 296 750 15
13 XM4607 296 100 15
14 XM4607 5 0
15 XM4607 10 5
16 XM8607 943 5 60
17 XJ2607 15 120
18 XJ2607 15 10
19 QPU607 5 2
10 XM6607 495 90 600

Result:
Workcenter Cycle time
MCB607 35
XJ2607 15
QPU607 7
XM4607 1920
XM8607 65
XJ2607 160
QPU607 7
XM6607 690

.... so did your example which is why I coded it as I did.

Workcenter Cycle time
MCB607 35
XJ2607 15
QPU607 7
XM4607 1920
XM8607 65
XJ2607 160
QPU607 7
XM6607 690


If you only want one figure per workcentre then use SUMPRODUCT

=SUMPRODUCT(--($A$2:$A$21=G2),($C$2:$C$21)+($D$2:$D$21))

where G2 contains the Workcenter ID.

HTH




"Cam" wrote:

Thanks Toppers,

Everything worked except, it lists a duplicate workcenter multiple times.

"Toppers" wrote:

VBA solution.

Assumes input on Sheet1, output on Sheet2

Sub Sumwkctr()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim r As Long, lastrow As Long, orow As Long
Dim sumtime As Double

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

orow = 1
ws2.Cells(orow, 1).Resize(1, 2) = Array("WorkCentre", "Cycle Time")

With ws1

lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
wkctr = .Cells(2, 1)
sumtime = 0
For r = 2 To lastrow
If .Cells(r, 1) < wkctr Then
orow = orow + 1
ws2.Cells(orow, 1) = wkctr
ws2.Cells(orow, 2) = sumtime

wkctr = .Cells(r, 1)
sumtime = 0

End If
sumtime = sumtime + .Cells(r, 3) + .Cells(r, 4)
Next r

orow = orow + 1
ws2.Cells(orow, 1) = wkctr
ws2.Cells(orow, 2) = sumtime
End With


End Sub


"Cam" wrote:

Hi,

I have an Excel file with the following info. I need to calculate the
cycletime (setup + run time) for each Workceter.
What function I need to lookup at column A, put a workcenter (only once if
multiple) in a cell, then calculate the total cycletime for each workcenter.
Thanks

DATA:
A B C D
1 Workcenter Machine Setup Run time
2 MCB607 5 30
3 XJ2607 5 10
4 QPU607 5 2
5 XM4607 15 5
6 XM4607 296 815 15
7 XM4607 296 5 0
8 XM4607 296 110 15
9 XM4607 5 0
10 XM4607 10 5
11 XM4607 15 5
12 XM4607 296 750 15
13 XM4607 296 100 15
14 XM4607 5 0
15 XM4607 10 5
16 XM8607 943 5 60
17 XJ2607 15 120
18 XJ2607 15 10
19 QPU607 5 2
10 XM6607 495 90 600

Result:
Workcenter Cycle time
MCB607 35
XJ2607 15
QPU607 7
XM4607 1920
XM8607 65
XJ2607 160
QPU607 7
XM6607 690

Hi Toppers again,

Sorry, I didn't make this clear. The result on worksheet 2 I want is:
Workcenter Setup Run time Cycle time
MCB607 5 30 35
QPU607 10 4 14
XJ2607 35 140 175
XM4607 2920 120 3040
XM6607 90 90 180
XM8607 5 5 10

where QPU607, XJ2607, XM4607, etcc (workcenter w/ multiple row only appear
once). What can I do to change to code to give me the result. Thanks


"Toppers" wrote:

... so did your example which is why I coded it as I did.

Workcenter Cycle time
MCB607 35
XJ2607 15
QPU607 7
XM4607 1920
XM8607 65
XJ2607 160
QPU607 7
XM6607 690


If you only want one figure per workcentre then use SUMPRODUCT

=SUMPRODUCT(--($A$2:$A$21=G2),($C$2:$C$21)+($D$2:$D$21))

where G2 contains the Workcenter ID.

HTH




"Cam" wrote:

Thanks Toppers,

Everything worked except, it lists a duplicate workcenter multiple times.

"Toppers" wrote:

VBA solution.

Assumes input on Sheet1, output on Sheet2

Sub Sumwkctr()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim r As Long, lastrow As Long, orow As Long
Dim sumtime As Double

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

orow = 1
ws2.Cells(orow, 1).Resize(1, 2) = Array("WorkCentre", "Cycle Time")

With ws1

lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
wkctr = .Cells(2, 1)
sumtime = 0
For r = 2 To lastrow
If .Cells(r, 1) < wkctr Then
orow = orow + 1
ws2.Cells(orow, 1) = wkctr
ws2.Cells(orow, 2) = sumtime

wkctr = .Cells(r, 1)
sumtime = 0

End If
sumtime = sumtime + .Cells(r, 3) + .Cells(r, 4)
Next r

orow = orow + 1
ws2.Cells(orow, 1) = wkctr
ws2.Cells(orow, 2) = sumtime
End With


End Sub


"Cam" wrote:

Hi,

I have an Excel file with the following info. I need to calculate the
cycletime (setup + run time) for each Workceter.
What function I need to lookup at column A, put a workcenter (only once if
multiple) in a cell, then calculate the total cycletime for each workcenter.
Thanks

DATA:
A B C D
1 Workcenter Machine Setup Run time
2 MCB607 5 30
3 XJ2607 5 10
4 QPU607 5 2
5 XM4607 15 5
6 XM4607 296 815 15
7 XM4607 296 5 0
8 XM4607 296 110 15
9 XM4607 5 0
10 XM4607 10 5
11 XM4607 15 5
12 XM4607 296 750 15
13 XM4607 296 100 15
14 XM4607 5 0
15 XM4607 10 5
16 XM8607 943 5 60
17 XJ2607 15 120
18 XJ2607 15 10
19 QPU607 5 2
10 XM6607 495 90 600

Result:
Workcenter Cycle time
MCB607 35
XJ2607 15
QPU607 7
XM4607 1920
XM8607 65
XJ2607 160
QPU607 7
XM6607 690

Assuming data on Sheet1 is columns A to D and same on Sheet2 A to D , then
on Sheet2:

in A2: first Workcenter ID

In B2: =SUMPRODUCT(--($A2=Sheet1!$A$2:$A$21),--(Sheet1!$C$2:$C$21))

in C2: =SUMPRODUCT(--($A2=Sheet1!$A$2:$A$21),--(Sheet1!$D$2:$D$21))

in D2: =B2+C2

Copy all these formulae down for the end of entries in column A

Change (length of )ranges to suit your data.

HTH

"Cam" wrote:

Hi Toppers again,

Sorry, I didn't make this clear. The result on worksheet 2 I want is:
Workcenter Setup Run time Cycle time
MCB607 5 30 35
QPU607 10 4 14
XJ2607 35 140 175
XM4607 2920 120 3040
XM6607 90 90 180
XM8607 5 5 10

where QPU607, XJ2607, XM4607, etcc (workcenter w/ multiple row only appear
once). What can I do to change to code to give me the result. Thanks


"Toppers" wrote:

... so did your example which is why I coded it as I did.

Workcenter Cycle time
MCB607 35
XJ2607 15
QPU607 7
XM4607 1920
XM8607 65
XJ2607 160
QPU607 7
XM6607 690


If you only want one figure per workcentre then use SUMPRODUCT

=SUMPRODUCT(--($A$2:$A$21=G2),($C$2:$C$21)+($D$2:$D$21))

where G2 contains the Workcenter ID.

HTH




"Cam" wrote:

Thanks Toppers,

Everything worked except, it lists a duplicate workcenter multiple times.

"Toppers" wrote:

VBA solution.

Assumes input on Sheet1, output on Sheet2

Sub Sumwkctr()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim r As Long, lastrow As Long, orow As Long
Dim sumtime As Double

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

orow = 1
ws2.Cells(orow, 1).Resize(1, 2) = Array("WorkCentre", "Cycle Time")

With ws1

lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
wkctr = .Cells(2, 1)
sumtime = 0
For r = 2 To lastrow
If .Cells(r, 1) < wkctr Then
orow = orow + 1
ws2.Cells(orow, 1) = wkctr
ws2.Cells(orow, 2) = sumtime

wkctr = .Cells(r, 1)
sumtime = 0

End If
sumtime = sumtime + .Cells(r, 3) + .Cells(r, 4)
Next r

orow = orow + 1
ws2.Cells(orow, 1) = wkctr
ws2.Cells(orow, 2) = sumtime
End With


End Sub


"Cam" wrote:

Hi,

I have an Excel file with the following info. I need to calculate the
cycletime (setup + run time) for each Workceter.
What function I need to lookup at column A, put a workcenter (only once if
multiple) in a cell, then calculate the total cycletime for each workcenter.
Thanks

DATA:
A B C D
1 Workcenter Machine Setup Run time
2 MCB607 5 30
3 XJ2607 5 10
4 QPU607 5 2
5 XM4607 15 5
6 XM4607 296 815 15
7 XM4607 296 5 0
8 XM4607 296 110 15
9 XM4607 5 0
10 XM4607 10 5
11 XM4607 15 5
12 XM4607 296 750 15
13 XM4607 296 100 15
14 XM4607 5 0
15 XM4607 10 5
16 XM8607 943 5 60
17 XJ2607 15 120
18 XJ2607 15 10
19 QPU607 5 2
10 XM6607 495 90 600

Result:
Workcenter Cycle time
MCB607 35
XJ2607 15
QPU607 7
XM4607 1920
XM8607 65
XJ2607 160
QPU607 7
XM6607 690