Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
How to match date criteria then calculate an average of matches | Excel Worksheet Functions | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
formula to calculate a 401K company match? | Excel Worksheet Functions |