|
Sort, Select and Avg Macro
Please help!
I have a report that is pulled regularly. It contains thirty day of data each time. Header row on line 11. Data starts on line 12. Column C & D are the identifiers of a particular sector. Each sector has 30 lines of data, one each for 30 days. Then the next sector starts. For each sector, I need to sort column E from greatest to least, then average the second thru the seventh value. In the data below, 1201A would be the first sector, 1201B is the second...I need to take each sector, sort the Indices greatest to least for that sector, ignore the first line of data after the sort, average the 2-7 lines of data, and ignore the rest of the data for the that sector. Then follow the same process with the next sector. There could be 50 to 100 sectors per report. Data looks like this: System Date Sector Num Sector ID Indices 1201/8 468 X 3-Jan 1201 A 24.46 1201/8 468 X 4-Jan 1201 A 25.49 1201/8 468 X 5-Jan 1201 A 26.16 1201/8 468 X 6-Jan 1201 A 25.39 1201/8 468 X 7-Jan 1201 A 25.34 1201/8 468 X 8-Jan 1201 A 25.38 1201/8 468 X 9-Jan 1201 A 24.75 1201/8 468 X 10-Jan 1201 A 24.39 1201/8 468 X 11-Jan 1201 A 25.68 1201/8 468 X 12-Jan 1201 A 25.22 1201/8 468 X 13-Jan 1201 A 25.19 1201/8 468 X 14-Jan 1201 A 26.38 1201/8 468 X 15-Jan 1201 A 25.22 1201/8 468 X 16-Jan 1201 A 24.33 1201/8 468 X 17-Jan 1201 A 24.63 1201/8 468 X 18-Jan 1201 A 25.00 1201/8 468 X 19-Jan 1201 A 25.58 1201/8 468 X 20-Jan 1201 A 25.40 1201/8 468 X 21-Jan 1201 A 25.38 1201/8 468 X 22-Jan 1201 A 25.43 1201/8 468 X 23-Jan 1201 A 24.29 1201/8 468 X 24-Jan 1201 A 24.39 1201/8 468 X 25-Jan 1201 A 26.04 1201/8 468 X 26-Jan 1201 A 25.72 1201/8 468 X 27-Jan 1201 A 26.32 1201/8 468 X 28-Jan 1201 A 25.10 1201/8 468 X 29-Jan 1201 A 26.42 1201/8 468 X 30-Jan 1201 A 24.88 1201/8 468 X 31-Jan 1201 A 24.47 1201/8 468 X 1-Feb 1201 A 25.16 1201/8 468 Y 3-Jan 1201 B 24.05 1201/8 468 Y 4-Jan 1201 B 26.54 1201/8 468 Y 5-Jan 1201 B 25.95 1201/8 468 Y 6-Jan 1201 B 26.62 1201/8 468 Y 7-Jan 1201 B 26.26 1201/8 468 Y 8-Jan 1201 B 26.79 1201/8 468 Y 9-Jan 1201 B 24.07 1201/8 468 Y 10-Jan 1201 B 24.13 1201/8 468 Y 11-Jan 1201 B 26.17 1201/8 468 Y 12-Jan 1201 B 25.58 1201/8 468 Y 13-Jan 1201 B 25.92 1201/8 468 Y 14-Jan 1201 B 25.74 1201/8 468 Y 15-Jan 1201 B 25.32 1201/8 468 Y 16-Jan 1201 B 24.17 1201/8 468 Y 17-Jan 1201 B 24.13 1201/8 468 Y 18-Jan 1201 B 25.18 1201/8 468 Y 19-Jan 1201 B 26.36 1201/8 468 Y 20-Jan 1201 B 26.01 1201/8 468 Y 21-Jan 1201 B 25.83 1201/8 468 Y 22-Jan 1201 B 26.28 1201/8 468 Y 23-Jan 1201 B 24.14 1201/8 468 Y 24-Jan 1201 B 24.26 1201/8 468 Y 25-Jan 1201 B 28.72 1201/8 468 Y 26-Jan 1201 B 26.51 1201/8 468 Y 27-Jan 1201 B 26.82 1201/8 468 Y 28-Jan 1201 B 26.17 1201/8 468 Y 29-Jan 1201 B 26.81 1201/8 468 Y 30-Jan 1201 B 24.08 1201/8 468 Y 31-Jan 1201 B 24.08 1201/8 468 Y 1-Feb 1201 B 25.39 1201/8 468 Z 3-Jan 1201 C 26.35 1201/8 468 Z 4-Jan 1201 C 30.81 1201/8 468 Z 5-Jan 1201 C 29.90 1201/8 468 Z 6-Jan 1201 C 28.09 1201/8 468 Z 7-Jan 1201 C 30.74 1201/8 468 Z 8-Jan 1201 C 29.06 1201/8 468 Z 9-Jan 1201 C 26.74 1201/8 468 Z 10-Jan 1201 C 27.60 1201/8 468 Z 11-Jan 1201 C 29.50 1201/8 468 Z 12-Jan 1201 C 28.72 1201/8 468 Z 13-Jan 1201 C 29.65 1201/8 468 Z 14-Jan 1201 C 28.57 1201/8 468 Z 15-Jan 1201 C 27.90 1201/8 468 Z 16-Jan 1201 C 27.01 1201/8 468 Z 17-Jan 1201 C 27.06 1201/8 468 Z 18-Jan 1201 C 28.57 1201/8 468 Z 19-Jan 1201 C 28.82 1201/8 468 Z 20-Jan 1201 C 29.21 1201/8 468 Z 21-Jan 1201 C 28.71 1201/8 468 Z 22-Jan 1201 C 29.07 1201/8 468 Z 23-Jan 1201 C 27.72 1201/8 468 Z 24-Jan 1201 C 26.92 1201/8 468 Z 25-Jan 1201 C 31.24 1201/8 468 Z 26-Jan 1201 C 30.49 1201/8 468 Z 27-Jan 1201 C 29.54 1201/8 468 Z 28-Jan 1201 C 29.10 1201/8 468 Z 29-Jan 1201 C 31.49 1201/8 468 Z 30-Jan 1201 C 25.69 1201/8 468 Z 31-Jan 1201 C 25.78 1201/8 468 Z 1-Feb 1201 C 29.31 I'd like the output to create a new sheet with three columns: Sector Num, Sector ID, Indices Average. In the example above, my output would be... Sector Num Sector ID Indices Avg 1201 A 26.05 1201 B 26.68 1201 C 30.47 Can anyone help?? It take so much time to do this manually for each report!! Thanks! |
Sort, Select and Avg Macro
Nicely framed question!
My results are a tad different to yours Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 2 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), order1:=xlDescending, header:=xlNo Next i NextRow = 2 For i = 2 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub HTH Bob "Amy" wrote in message ... Please help! I have a report that is pulled regularly. It contains thirty day of data each time. Header row on line 11. Data starts on line 12. Column C & D are the identifiers of a particular sector. Each sector has 30 lines of data, one each for 30 days. Then the next sector starts. For each sector, I need to sort column E from greatest to least, then average the second thru the seventh value. In the data below, 1201A would be the first sector, 1201B is the second...I need to take each sector, sort the Indices greatest to least for that sector, ignore the first line of data after the sort, average the 2-7 lines of data, and ignore the rest of the data for the that sector. Then follow the same process with the next sector. There could be 50 to 100 sectors per report. Data looks like this: System Date Sector Num Sector ID Indices 1201/8 468 X 3-Jan 1201 A 24.46 1201/8 468 X 4-Jan 1201 A 25.49 1201/8 468 X 5-Jan 1201 A 26.16 1201/8 468 X 6-Jan 1201 A 25.39 1201/8 468 X 7-Jan 1201 A 25.34 1201/8 468 X 8-Jan 1201 A 25.38 1201/8 468 X 9-Jan 1201 A 24.75 1201/8 468 X 10-Jan 1201 A 24.39 1201/8 468 X 11-Jan 1201 A 25.68 1201/8 468 X 12-Jan 1201 A 25.22 1201/8 468 X 13-Jan 1201 A 25.19 1201/8 468 X 14-Jan 1201 A 26.38 1201/8 468 X 15-Jan 1201 A 25.22 1201/8 468 X 16-Jan 1201 A 24.33 1201/8 468 X 17-Jan 1201 A 24.63 1201/8 468 X 18-Jan 1201 A 25.00 1201/8 468 X 19-Jan 1201 A 25.58 1201/8 468 X 20-Jan 1201 A 25.40 1201/8 468 X 21-Jan 1201 A 25.38 1201/8 468 X 22-Jan 1201 A 25.43 1201/8 468 X 23-Jan 1201 A 24.29 1201/8 468 X 24-Jan 1201 A 24.39 1201/8 468 X 25-Jan 1201 A 26.04 1201/8 468 X 26-Jan 1201 A 25.72 1201/8 468 X 27-Jan 1201 A 26.32 1201/8 468 X 28-Jan 1201 A 25.10 1201/8 468 X 29-Jan 1201 A 26.42 1201/8 468 X 30-Jan 1201 A 24.88 1201/8 468 X 31-Jan 1201 A 24.47 1201/8 468 X 1-Feb 1201 A 25.16 1201/8 468 Y 3-Jan 1201 B 24.05 1201/8 468 Y 4-Jan 1201 B 26.54 1201/8 468 Y 5-Jan 1201 B 25.95 1201/8 468 Y 6-Jan 1201 B 26.62 1201/8 468 Y 7-Jan 1201 B 26.26 1201/8 468 Y 8-Jan 1201 B 26.79 1201/8 468 Y 9-Jan 1201 B 24.07 1201/8 468 Y 10-Jan 1201 B 24.13 1201/8 468 Y 11-Jan 1201 B 26.17 1201/8 468 Y 12-Jan 1201 B 25.58 1201/8 468 Y 13-Jan 1201 B 25.92 1201/8 468 Y 14-Jan 1201 B 25.74 1201/8 468 Y 15-Jan 1201 B 25.32 1201/8 468 Y 16-Jan 1201 B 24.17 1201/8 468 Y 17-Jan 1201 B 24.13 1201/8 468 Y 18-Jan 1201 B 25.18 1201/8 468 Y 19-Jan 1201 B 26.36 1201/8 468 Y 20-Jan 1201 B 26.01 1201/8 468 Y 21-Jan 1201 B 25.83 1201/8 468 Y 22-Jan 1201 B 26.28 1201/8 468 Y 23-Jan 1201 B 24.14 1201/8 468 Y 24-Jan 1201 B 24.26 1201/8 468 Y 25-Jan 1201 B 28.72 1201/8 468 Y 26-Jan 1201 B 26.51 1201/8 468 Y 27-Jan 1201 B 26.82 1201/8 468 Y 28-Jan 1201 B 26.17 1201/8 468 Y 29-Jan 1201 B 26.81 1201/8 468 Y 30-Jan 1201 B 24.08 1201/8 468 Y 31-Jan 1201 B 24.08 1201/8 468 Y 1-Feb 1201 B 25.39 1201/8 468 Z 3-Jan 1201 C 26.35 1201/8 468 Z 4-Jan 1201 C 30.81 1201/8 468 Z 5-Jan 1201 C 29.90 1201/8 468 Z 6-Jan 1201 C 28.09 1201/8 468 Z 7-Jan 1201 C 30.74 1201/8 468 Z 8-Jan 1201 C 29.06 1201/8 468 Z 9-Jan 1201 C 26.74 1201/8 468 Z 10-Jan 1201 C 27.60 1201/8 468 Z 11-Jan 1201 C 29.50 1201/8 468 Z 12-Jan 1201 C 28.72 1201/8 468 Z 13-Jan 1201 C 29.65 1201/8 468 Z 14-Jan 1201 C 28.57 1201/8 468 Z 15-Jan 1201 C 27.90 1201/8 468 Z 16-Jan 1201 C 27.01 1201/8 468 Z 17-Jan 1201 C 27.06 1201/8 468 Z 18-Jan 1201 C 28.57 1201/8 468 Z 19-Jan 1201 C 28.82 1201/8 468 Z 20-Jan 1201 C 29.21 1201/8 468 Z 21-Jan 1201 C 28.71 1201/8 468 Z 22-Jan 1201 C 29.07 1201/8 468 Z 23-Jan 1201 C 27.72 1201/8 468 Z 24-Jan 1201 C 26.92 1201/8 468 Z 25-Jan 1201 C 31.24 1201/8 468 Z 26-Jan 1201 C 30.49 1201/8 468 Z 27-Jan 1201 C 29.54 1201/8 468 Z 28-Jan 1201 C 29.10 1201/8 468 Z 29-Jan 1201 C 31.49 1201/8 468 Z 30-Jan 1201 C 25.69 1201/8 468 Z 31-Jan 1201 C 25.78 1201/8 468 Z 1-Feb 1201 C 29.31 I'd like the output to create a new sheet with three columns: Sector Num, Sector ID, Indices Average. In the example above, my output would be... Sector Num Sector ID Indices Avg 1201 A 26.05 1201 B 26.68 1201 C 30.47 Can anyone help?? It take so much time to do this manually for each report!! Thanks! |
Sort, Select and Avg Macro
Watch the word wrap in the line after the sort
Bob "Bob Phillips" wrote in message ... Nicely framed question! My results are a tad different to yours Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 2 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), order1:=xlDescending, header:=xlNo Next i NextRow = 2 For i = 2 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub HTH Bob "Amy" wrote in message ... Please help! I have a report that is pulled regularly. It contains thirty day of data each time. Header row on line 11. Data starts on line 12. Column C & D are the identifiers of a particular sector. Each sector has 30 lines of data, one each for 30 days. Then the next sector starts. For each sector, I need to sort column E from greatest to least, then average the second thru the seventh value. In the data below, 1201A would be the first sector, 1201B is the second...I need to take each sector, sort the Indices greatest to least for that sector, ignore the first line of data after the sort, average the 2-7 lines of data, and ignore the rest of the data for the that sector. Then follow the same process with the next sector. There could be 50 to 100 sectors per report. Data looks like this: System Date Sector Num Sector ID Indices 1201/8 468 X 3-Jan 1201 A 24.46 1201/8 468 X 4-Jan 1201 A 25.49 1201/8 468 X 5-Jan 1201 A 26.16 1201/8 468 X 6-Jan 1201 A 25.39 1201/8 468 X 7-Jan 1201 A 25.34 1201/8 468 X 8-Jan 1201 A 25.38 1201/8 468 X 9-Jan 1201 A 24.75 1201/8 468 X 10-Jan 1201 A 24.39 1201/8 468 X 11-Jan 1201 A 25.68 1201/8 468 X 12-Jan 1201 A 25.22 1201/8 468 X 13-Jan 1201 A 25.19 1201/8 468 X 14-Jan 1201 A 26.38 1201/8 468 X 15-Jan 1201 A 25.22 1201/8 468 X 16-Jan 1201 A 24.33 1201/8 468 X 17-Jan 1201 A 24.63 1201/8 468 X 18-Jan 1201 A 25.00 1201/8 468 X 19-Jan 1201 A 25.58 1201/8 468 X 20-Jan 1201 A 25.40 1201/8 468 X 21-Jan 1201 A 25.38 1201/8 468 X 22-Jan 1201 A 25.43 1201/8 468 X 23-Jan 1201 A 24.29 1201/8 468 X 24-Jan 1201 A 24.39 1201/8 468 X 25-Jan 1201 A 26.04 1201/8 468 X 26-Jan 1201 A 25.72 1201/8 468 X 27-Jan 1201 A 26.32 1201/8 468 X 28-Jan 1201 A 25.10 1201/8 468 X 29-Jan 1201 A 26.42 1201/8 468 X 30-Jan 1201 A 24.88 1201/8 468 X 31-Jan 1201 A 24.47 1201/8 468 X 1-Feb 1201 A 25.16 1201/8 468 Y 3-Jan 1201 B 24.05 1201/8 468 Y 4-Jan 1201 B 26.54 1201/8 468 Y 5-Jan 1201 B 25.95 1201/8 468 Y 6-Jan 1201 B 26.62 1201/8 468 Y 7-Jan 1201 B 26.26 1201/8 468 Y 8-Jan 1201 B 26.79 1201/8 468 Y 9-Jan 1201 B 24.07 1201/8 468 Y 10-Jan 1201 B 24.13 1201/8 468 Y 11-Jan 1201 B 26.17 1201/8 468 Y 12-Jan 1201 B 25.58 1201/8 468 Y 13-Jan 1201 B 25.92 1201/8 468 Y 14-Jan 1201 B 25.74 1201/8 468 Y 15-Jan 1201 B 25.32 1201/8 468 Y 16-Jan 1201 B 24.17 1201/8 468 Y 17-Jan 1201 B 24.13 1201/8 468 Y 18-Jan 1201 B 25.18 1201/8 468 Y 19-Jan 1201 B 26.36 1201/8 468 Y 20-Jan 1201 B 26.01 1201/8 468 Y 21-Jan 1201 B 25.83 1201/8 468 Y 22-Jan 1201 B 26.28 1201/8 468 Y 23-Jan 1201 B 24.14 1201/8 468 Y 24-Jan 1201 B 24.26 1201/8 468 Y 25-Jan 1201 B 28.72 1201/8 468 Y 26-Jan 1201 B 26.51 1201/8 468 Y 27-Jan 1201 B 26.82 1201/8 468 Y 28-Jan 1201 B 26.17 1201/8 468 Y 29-Jan 1201 B 26.81 1201/8 468 Y 30-Jan 1201 B 24.08 1201/8 468 Y 31-Jan 1201 B 24.08 1201/8 468 Y 1-Feb 1201 B 25.39 1201/8 468 Z 3-Jan 1201 C 26.35 1201/8 468 Z 4-Jan 1201 C 30.81 1201/8 468 Z 5-Jan 1201 C 29.90 1201/8 468 Z 6-Jan 1201 C 28.09 1201/8 468 Z 7-Jan 1201 C 30.74 1201/8 468 Z 8-Jan 1201 C 29.06 1201/8 468 Z 9-Jan 1201 C 26.74 1201/8 468 Z 10-Jan 1201 C 27.60 1201/8 468 Z 11-Jan 1201 C 29.50 1201/8 468 Z 12-Jan 1201 C 28.72 1201/8 468 Z 13-Jan 1201 C 29.65 1201/8 468 Z 14-Jan 1201 C 28.57 1201/8 468 Z 15-Jan 1201 C 27.90 1201/8 468 Z 16-Jan 1201 C 27.01 1201/8 468 Z 17-Jan 1201 C 27.06 1201/8 468 Z 18-Jan 1201 C 28.57 1201/8 468 Z 19-Jan 1201 C 28.82 1201/8 468 Z 20-Jan 1201 C 29.21 1201/8 468 Z 21-Jan 1201 C 28.71 1201/8 468 Z 22-Jan 1201 C 29.07 1201/8 468 Z 23-Jan 1201 C 27.72 1201/8 468 Z 24-Jan 1201 C 26.92 1201/8 468 Z 25-Jan 1201 C 31.24 1201/8 468 Z 26-Jan 1201 C 30.49 1201/8 468 Z 27-Jan 1201 C 29.54 1201/8 468 Z 28-Jan 1201 C 29.10 1201/8 468 Z 29-Jan 1201 C 31.49 1201/8 468 Z 30-Jan 1201 C 25.69 1201/8 468 Z 31-Jan 1201 C 25.78 1201/8 468 Z 1-Feb 1201 C 29.31 I'd like the output to create a new sheet with three columns: Sector Num, Sector ID, Indices Average. In the example above, my output would be... Sector Num Sector ID Indices Avg 1201 A 26.05 1201 B 26.68 1201 C 30.47 Can anyone help?? It take so much time to do this manually for each report!! Thanks! |
Sort, Select and Avg Macro
Thanks, Bob!
I got the word wrap fixed up but it keeps erroring out at : Worksheets("Sheet2").Cells(NextRow, "A") I get a "compile error" Expected: = Any thoughts? Amy "Bob Phillips" wrote: Watch the word wrap in the line after the sort Bob "Bob Phillips" wrote in message ... Nicely framed question! My results are a tad different to yours Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 2 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), order1:=xlDescending, header:=xlNo Next i NextRow = 2 For i = 2 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub HTH Bob "Amy" wrote in message ... Please help! I have a report that is pulled regularly. It contains thirty day of data each time. Header row on line 11. Data starts on line 12. Column C & D are the identifiers of a particular sector. Each sector has 30 lines of data, one each for 30 days. Then the next sector starts. For each sector, I need to sort column E from greatest to least, then average the second thru the seventh value. In the data below, 1201A would be the first sector, 1201B is the second...I need to take each sector, sort the Indices greatest to least for that sector, ignore the first line of data after the sort, average the 2-7 lines of data, and ignore the rest of the data for the that sector. Then follow the same process with the next sector. There could be 50 to 100 sectors per report. Data looks like this: System Date Sector Num Sector ID Indices 1201/8 468 X 3-Jan 1201 A 24.46 1201/8 468 X 4-Jan 1201 A 25.49 1201/8 468 X 5-Jan 1201 A 26.16 1201/8 468 X 6-Jan 1201 A 25.39 1201/8 468 X 7-Jan 1201 A 25.34 1201/8 468 X 8-Jan 1201 A 25.38 1201/8 468 X 9-Jan 1201 A 24.75 1201/8 468 X 10-Jan 1201 A 24.39 1201/8 468 X 11-Jan 1201 A 25.68 1201/8 468 X 12-Jan 1201 A 25.22 1201/8 468 X 13-Jan 1201 A 25.19 1201/8 468 X 14-Jan 1201 A 26.38 1201/8 468 X 15-Jan 1201 A 25.22 1201/8 468 X 16-Jan 1201 A 24.33 1201/8 468 X 17-Jan 1201 A 24.63 1201/8 468 X 18-Jan 1201 A 25.00 1201/8 468 X 19-Jan 1201 A 25.58 1201/8 468 X 20-Jan 1201 A 25.40 1201/8 468 X 21-Jan 1201 A 25.38 1201/8 468 X 22-Jan 1201 A 25.43 1201/8 468 X 23-Jan 1201 A 24.29 1201/8 468 X 24-Jan 1201 A 24.39 1201/8 468 X 25-Jan 1201 A 26.04 1201/8 468 X 26-Jan 1201 A 25.72 1201/8 468 X 27-Jan 1201 A 26.32 1201/8 468 X 28-Jan 1201 A 25.10 1201/8 468 X 29-Jan 1201 A 26.42 1201/8 468 X 30-Jan 1201 A 24.88 1201/8 468 X 31-Jan 1201 A 24.47 1201/8 468 X 1-Feb 1201 A 25.16 1201/8 468 Y 3-Jan 1201 B 24.05 1201/8 468 Y 4-Jan 1201 B 26.54 1201/8 468 Y 5-Jan 1201 B 25.95 1201/8 468 Y 6-Jan 1201 B 26.62 1201/8 468 Y 7-Jan 1201 B 26.26 1201/8 468 Y 8-Jan 1201 B 26.79 1201/8 468 Y 9-Jan 1201 B 24.07 1201/8 468 Y 10-Jan 1201 B 24.13 1201/8 468 Y 11-Jan 1201 B 26.17 1201/8 468 Y 12-Jan 1201 B 25.58 1201/8 468 Y 13-Jan 1201 B 25.92 1201/8 468 Y 14-Jan 1201 B 25.74 1201/8 468 Y 15-Jan 1201 B 25.32 1201/8 468 Y 16-Jan 1201 B 24.17 1201/8 468 Y 17-Jan 1201 B 24.13 1201/8 468 Y 18-Jan 1201 B 25.18 1201/8 468 Y 19-Jan 1201 B 26.36 1201/8 468 Y 20-Jan 1201 B 26.01 1201/8 468 Y 21-Jan 1201 B 25.83 1201/8 468 Y 22-Jan 1201 B 26.28 1201/8 468 Y 23-Jan 1201 B 24.14 1201/8 468 Y 24-Jan 1201 B 24.26 1201/8 468 Y 25-Jan 1201 B 28.72 1201/8 468 Y 26-Jan 1201 B 26.51 1201/8 468 Y 27-Jan 1201 B 26.82 1201/8 468 Y 28-Jan 1201 B 26.17 1201/8 468 Y 29-Jan 1201 B 26.81 1201/8 468 Y 30-Jan 1201 B 24.08 1201/8 468 Y 31-Jan 1201 B 24.08 1201/8 468 Y 1-Feb 1201 B 25.39 1201/8 468 Z 3-Jan 1201 C 26.35 1201/8 468 Z 4-Jan 1201 C 30.81 1201/8 468 Z 5-Jan 1201 C 29.90 1201/8 468 Z 6-Jan 1201 C 28.09 1201/8 468 Z 7-Jan 1201 C 30.74 1201/8 468 Z 8-Jan 1201 C 29.06 1201/8 468 Z 9-Jan 1201 C 26.74 1201/8 468 Z 10-Jan 1201 C 27.60 1201/8 468 Z 11-Jan 1201 C 29.50 1201/8 468 Z 12-Jan 1201 C 28.72 1201/8 468 Z 13-Jan 1201 C 29.65 1201/8 468 Z 14-Jan 1201 C 28.57 1201/8 468 Z 15-Jan 1201 C 27.90 1201/8 468 Z 16-Jan 1201 C 27.01 1201/8 468 Z 17-Jan 1201 C 27.06 1201/8 468 Z 18-Jan 1201 C 28.57 1201/8 468 Z 19-Jan 1201 C 28.82 1201/8 468 Z 20-Jan 1201 C 29.21 1201/8 468 Z 21-Jan 1201 C 28.71 1201/8 468 Z 22-Jan 1201 C 29.07 1201/8 468 Z 23-Jan 1201 C 27.72 1201/8 468 Z 24-Jan 1201 C 26.92 1201/8 468 Z 25-Jan 1201 C 31.24 1201/8 468 Z 26-Jan 1201 C 30.49 1201/8 468 Z 27-Jan 1201 C 29.54 1201/8 468 Z 28-Jan 1201 C 29.10 1201/8 468 Z 29-Jan 1201 C 31.49 1201/8 468 Z 30-Jan 1201 C 25.69 1201/8 468 Z 31-Jan 1201 C 25.78 1201/8 468 Z 1-Feb 1201 C 29.31 I'd like the output to create a new sheet with three columns: Sector Num, Sector ID, Indices Average. In the example above, my output would be... Sector Num Sector ID Indices Avg 1201 A 26.05 1201 B 26.68 1201 C 30.47 Can anyone help?? It take so much time to do this manually for each report!! Thanks! . |
Sort, Select and Avg Macro
The line "For i = 2 To LastRow Step 30" should read "For i = 12 To LastRow
Step 30" in both places since you said your data started on Row 12. -- Frank K "Amy" wrote: Thanks, Bob! I got the word wrap fixed up but it keeps erroring out at : Worksheets("Sheet2").Cells(NextRow, "A") I get a "compile error" Expected: = Any thoughts? Amy "Bob Phillips" wrote: Watch the word wrap in the line after the sort Bob "Bob Phillips" wrote in message ... Nicely framed question! My results are a tad different to yours Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 2 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), order1:=xlDescending, header:=xlNo Next i NextRow = 2 For i = 2 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub HTH Bob "Amy" wrote in message ... Please help! I have a report that is pulled regularly. It contains thirty day of data each time. Header row on line 11. Data starts on line 12. Column C & D are the identifiers of a particular sector. Each sector has 30 lines of data, one each for 30 days. Then the next sector starts. For each sector, I need to sort column E from greatest to least, then average the second thru the seventh value. In the data below, 1201A would be the first sector, 1201B is the second...I need to take each sector, sort the Indices greatest to least for that sector, ignore the first line of data after the sort, average the 2-7 lines of data, and ignore the rest of the data for the that sector. Then follow the same process with the next sector. There could be 50 to 100 sectors per report. Data looks like this: System Date Sector Num Sector ID Indices 1201/8 468 X 3-Jan 1201 A 24.46 1201/8 468 X 4-Jan 1201 A 25.49 1201/8 468 X 5-Jan 1201 A 26.16 1201/8 468 X 6-Jan 1201 A 25.39 1201/8 468 X 7-Jan 1201 A 25.34 1201/8 468 X 8-Jan 1201 A 25.38 1201/8 468 X 9-Jan 1201 A 24.75 1201/8 468 X 10-Jan 1201 A 24.39 1201/8 468 X 11-Jan 1201 A 25.68 1201/8 468 X 12-Jan 1201 A 25.22 1201/8 468 X 13-Jan 1201 A 25.19 1201/8 468 X 14-Jan 1201 A 26.38 1201/8 468 X 15-Jan 1201 A 25.22 1201/8 468 X 16-Jan 1201 A 24.33 1201/8 468 X 17-Jan 1201 A 24.63 1201/8 468 X 18-Jan 1201 A 25.00 1201/8 468 X 19-Jan 1201 A 25.58 1201/8 468 X 20-Jan 1201 A 25.40 1201/8 468 X 21-Jan 1201 A 25.38 1201/8 468 X 22-Jan 1201 A 25.43 1201/8 468 X 23-Jan 1201 A 24.29 1201/8 468 X 24-Jan 1201 A 24.39 1201/8 468 X 25-Jan 1201 A 26.04 1201/8 468 X 26-Jan 1201 A 25.72 1201/8 468 X 27-Jan 1201 A 26.32 1201/8 468 X 28-Jan 1201 A 25.10 1201/8 468 X 29-Jan 1201 A 26.42 1201/8 468 X 30-Jan 1201 A 24.88 1201/8 468 X 31-Jan 1201 A 24.47 1201/8 468 X 1-Feb 1201 A 25.16 1201/8 468 Y 3-Jan 1201 B 24.05 1201/8 468 Y 4-Jan 1201 B 26.54 1201/8 468 Y 5-Jan 1201 B 25.95 1201/8 468 Y 6-Jan 1201 B 26.62 1201/8 468 Y 7-Jan 1201 B 26.26 1201/8 468 Y 8-Jan 1201 B 26.79 1201/8 468 Y 9-Jan 1201 B 24.07 1201/8 468 Y 10-Jan 1201 B 24.13 1201/8 468 Y 11-Jan 1201 B 26.17 1201/8 468 Y 12-Jan 1201 B 25.58 1201/8 468 Y 13-Jan 1201 B 25.92 1201/8 468 Y 14-Jan 1201 B 25.74 1201/8 468 Y 15-Jan 1201 B 25.32 1201/8 468 Y 16-Jan 1201 B 24.17 1201/8 468 Y 17-Jan 1201 B 24.13 1201/8 468 Y 18-Jan 1201 B 25.18 1201/8 468 Y 19-Jan 1201 B 26.36 1201/8 468 Y 20-Jan 1201 B 26.01 1201/8 468 Y 21-Jan 1201 B 25.83 1201/8 468 Y 22-Jan 1201 B 26.28 1201/8 468 Y 23-Jan 1201 B 24.14 1201/8 468 Y 24-Jan 1201 B 24.26 1201/8 468 Y 25-Jan 1201 B 28.72 1201/8 468 Y 26-Jan 1201 B 26.51 1201/8 468 Y 27-Jan 1201 B 26.82 1201/8 468 Y 28-Jan 1201 B 26.17 1201/8 468 Y 29-Jan 1201 B 26.81 1201/8 468 Y 30-Jan 1201 B 24.08 1201/8 468 Y 31-Jan 1201 B 24.08 1201/8 468 Y 1-Feb 1201 B 25.39 1201/8 468 Z 3-Jan 1201 C 26.35 1201/8 468 Z 4-Jan 1201 C 30.81 1201/8 468 Z 5-Jan 1201 C 29.90 1201/8 468 Z 6-Jan 1201 C 28.09 1201/8 468 Z 7-Jan 1201 C 30.74 1201/8 468 Z 8-Jan 1201 C 29.06 1201/8 468 Z 9-Jan 1201 C 26.74 1201/8 468 Z 10-Jan 1201 C 27.60 1201/8 468 Z 11-Jan 1201 C 29.50 1201/8 468 Z 12-Jan 1201 C 28.72 1201/8 468 Z 13-Jan 1201 C 29.65 1201/8 468 Z 14-Jan 1201 C 28.57 1201/8 468 Z 15-Jan 1201 C 27.90 1201/8 468 Z 16-Jan 1201 C 27.01 1201/8 468 Z 17-Jan 1201 C 27.06 1201/8 468 Z 18-Jan 1201 C 28.57 1201/8 468 Z 19-Jan 1201 C 28.82 1201/8 468 Z 20-Jan 1201 C 29.21 1201/8 468 Z 21-Jan 1201 C 28.71 1201/8 468 Z 22-Jan 1201 C 29.07 1201/8 468 Z 23-Jan 1201 C 27.72 1201/8 468 Z 24-Jan 1201 C 26.92 1201/8 468 Z 25-Jan 1201 C 31.24 1201/8 468 Z 26-Jan 1201 C 30.49 1201/8 468 Z 27-Jan 1201 C 29.54 1201/8 468 Z 28-Jan 1201 C 29.10 1201/8 468 Z 29-Jan 1201 C 31.49 1201/8 468 Z 30-Jan 1201 C 25.69 1201/8 468 Z 31-Jan 1201 C 25.78 1201/8 468 Z 1-Feb 1201 C 29.31 I'd like the output to create a new sheet with three columns: Sector Num, Sector ID, Indices Average. In the example above, my output would be... Sector Num Sector ID Indices Avg 1201 A 26.05 1201 B 26.68 1201 C 30.47 Can anyone help?? It take so much time to do this manually for each report!! Thanks! . |
Sort, Select and Avg Macro
There is other word-wrap Amy which needs correcting. Try this version with
continuations t overcome the wrap (and which also starts at row 12 as Frank pointed out Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 2 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), _ order1:=xlDescending, _ header:=xlNo Next i NextRow = 2 For i = 12 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = _ Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy _ Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = _ Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub "Amy" wrote in message ... Thanks, Bob! I got the word wrap fixed up but it keeps erroring out at : Worksheets("Sheet2").Cells(NextRow, "A") I get a "compile error" Expected: = Any thoughts? Amy "Bob Phillips" wrote: Watch the word wrap in the line after the sort Bob "Bob Phillips" wrote in message ... Nicely framed question! My results are a tad different to yours Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 2 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), order1:=xlDescending, header:=xlNo Next i NextRow = 2 For i = 2 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub HTH Bob "Amy" wrote in message ... Please help! I have a report that is pulled regularly. It contains thirty day of data each time. Header row on line 11. Data starts on line 12. Column C & D are the identifiers of a particular sector. Each sector has 30 lines of data, one each for 30 days. Then the next sector starts. For each sector, I need to sort column E from greatest to least, then average the second thru the seventh value. In the data below, 1201A would be the first sector, 1201B is the second...I need to take each sector, sort the Indices greatest to least for that sector, ignore the first line of data after the sort, average the 2-7 lines of data, and ignore the rest of the data for the that sector. Then follow the same process with the next sector. There could be 50 to 100 sectors per report. Data looks like this: System Date Sector Num Sector ID Indices 1201/8 468 X 3-Jan 1201 A 24.46 1201/8 468 X 4-Jan 1201 A 25.49 1201/8 468 X 5-Jan 1201 A 26.16 1201/8 468 X 6-Jan 1201 A 25.39 1201/8 468 X 7-Jan 1201 A 25.34 1201/8 468 X 8-Jan 1201 A 25.38 1201/8 468 X 9-Jan 1201 A 24.75 1201/8 468 X 10-Jan 1201 A 24.39 1201/8 468 X 11-Jan 1201 A 25.68 1201/8 468 X 12-Jan 1201 A 25.22 1201/8 468 X 13-Jan 1201 A 25.19 1201/8 468 X 14-Jan 1201 A 26.38 1201/8 468 X 15-Jan 1201 A 25.22 1201/8 468 X 16-Jan 1201 A 24.33 1201/8 468 X 17-Jan 1201 A 24.63 1201/8 468 X 18-Jan 1201 A 25.00 1201/8 468 X 19-Jan 1201 A 25.58 1201/8 468 X 20-Jan 1201 A 25.40 1201/8 468 X 21-Jan 1201 A 25.38 1201/8 468 X 22-Jan 1201 A 25.43 1201/8 468 X 23-Jan 1201 A 24.29 1201/8 468 X 24-Jan 1201 A 24.39 1201/8 468 X 25-Jan 1201 A 26.04 1201/8 468 X 26-Jan 1201 A 25.72 1201/8 468 X 27-Jan 1201 A 26.32 1201/8 468 X 28-Jan 1201 A 25.10 1201/8 468 X 29-Jan 1201 A 26.42 1201/8 468 X 30-Jan 1201 A 24.88 1201/8 468 X 31-Jan 1201 A 24.47 1201/8 468 X 1-Feb 1201 A 25.16 1201/8 468 Y 3-Jan 1201 B 24.05 1201/8 468 Y 4-Jan 1201 B 26.54 1201/8 468 Y 5-Jan 1201 B 25.95 1201/8 468 Y 6-Jan 1201 B 26.62 1201/8 468 Y 7-Jan 1201 B 26.26 1201/8 468 Y 8-Jan 1201 B 26.79 1201/8 468 Y 9-Jan 1201 B 24.07 1201/8 468 Y 10-Jan 1201 B 24.13 1201/8 468 Y 11-Jan 1201 B 26.17 1201/8 468 Y 12-Jan 1201 B 25.58 1201/8 468 Y 13-Jan 1201 B 25.92 1201/8 468 Y 14-Jan 1201 B 25.74 1201/8 468 Y 15-Jan 1201 B 25.32 1201/8 468 Y 16-Jan 1201 B 24.17 1201/8 468 Y 17-Jan 1201 B 24.13 1201/8 468 Y 18-Jan 1201 B 25.18 1201/8 468 Y 19-Jan 1201 B 26.36 1201/8 468 Y 20-Jan 1201 B 26.01 1201/8 468 Y 21-Jan 1201 B 25.83 1201/8 468 Y 22-Jan 1201 B 26.28 1201/8 468 Y 23-Jan 1201 B 24.14 1201/8 468 Y 24-Jan 1201 B 24.26 1201/8 468 Y 25-Jan 1201 B 28.72 1201/8 468 Y 26-Jan 1201 B 26.51 1201/8 468 Y 27-Jan 1201 B 26.82 1201/8 468 Y 28-Jan 1201 B 26.17 1201/8 468 Y 29-Jan 1201 B 26.81 1201/8 468 Y 30-Jan 1201 B 24.08 1201/8 468 Y 31-Jan 1201 B 24.08 1201/8 468 Y 1-Feb 1201 B 25.39 1201/8 468 Z 3-Jan 1201 C 26.35 1201/8 468 Z 4-Jan 1201 C 30.81 1201/8 468 Z 5-Jan 1201 C 29.90 1201/8 468 Z 6-Jan 1201 C 28.09 1201/8 468 Z 7-Jan 1201 C 30.74 1201/8 468 Z 8-Jan 1201 C 29.06 1201/8 468 Z 9-Jan 1201 C 26.74 1201/8 468 Z 10-Jan 1201 C 27.60 1201/8 468 Z 11-Jan 1201 C 29.50 1201/8 468 Z 12-Jan 1201 C 28.72 1201/8 468 Z 13-Jan 1201 C 29.65 1201/8 468 Z 14-Jan 1201 C 28.57 1201/8 468 Z 15-Jan 1201 C 27.90 1201/8 468 Z 16-Jan 1201 C 27.01 1201/8 468 Z 17-Jan 1201 C 27.06 1201/8 468 Z 18-Jan 1201 C 28.57 1201/8 468 Z 19-Jan 1201 C 28.82 1201/8 468 Z 20-Jan 1201 C 29.21 1201/8 468 Z 21-Jan 1201 C 28.71 1201/8 468 Z 22-Jan 1201 C 29.07 1201/8 468 Z 23-Jan 1201 C 27.72 1201/8 468 Z 24-Jan 1201 C 26.92 1201/8 468 Z 25-Jan 1201 C 31.24 1201/8 468 Z 26-Jan 1201 C 30.49 1201/8 468 Z 27-Jan 1201 C 29.54 1201/8 468 Z 28-Jan 1201 C 29.10 1201/8 468 Z 29-Jan 1201 C 31.49 1201/8 468 Z 30-Jan 1201 C 25.69 1201/8 468 Z 31-Jan 1201 C 25.78 1201/8 468 Z 1-Feb 1201 C 29.31 I'd like the output to create a new sheet with three columns: Sector Num, Sector ID, Indices Average. In the example above, my output would be... Sector Num Sector ID Indices Avg 1201 A 26.05 1201 B 26.68 1201 C 30.47 Can anyone help?? It take so much time to do this manually for each report!! Thanks! . |
Sort, Select and Avg Macro
Oops, missed one of the startrows
Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 12 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), _ order1:=xlDescending, _ header:=xlNo Next i NextRow = 2 For i = 12 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = _ Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy _ Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = _ Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub Bob "Bob Phillips" wrote in message ... There is other word-wrap Amy which needs correcting. Try this version with continuations t overcome the wrap (and which also starts at row 12 as Frank pointed out Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 2 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), _ order1:=xlDescending, _ header:=xlNo Next i NextRow = 2 For i = 12 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = _ Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy _ Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = _ Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub "Amy" wrote in message ... Thanks, Bob! I got the word wrap fixed up but it keeps erroring out at : Worksheets("Sheet2").Cells(NextRow, "A") I get a "compile error" Expected: = Any thoughts? Amy "Bob Phillips" wrote: Watch the word wrap in the line after the sort Bob "Bob Phillips" wrote in message ... Nicely framed question! My results are a tad different to yours Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 2 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), order1:=xlDescending, header:=xlNo Next i NextRow = 2 For i = 2 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub HTH Bob "Amy" wrote in message ... Please help! I have a report that is pulled regularly. It contains thirty day of data each time. Header row on line 11. Data starts on line 12. Column C & D are the identifiers of a particular sector. Each sector has 30 lines of data, one each for 30 days. Then the next sector starts. For each sector, I need to sort column E from greatest to least, then average the second thru the seventh value. In the data below, 1201A would be the first sector, 1201B is the second...I need to take each sector, sort the Indices greatest to least for that sector, ignore the first line of data after the sort, average the 2-7 lines of data, and ignore the rest of the data for the that sector. Then follow the same process with the next sector. There could be 50 to 100 sectors per report. Data looks like this: System Date Sector Num Sector ID Indices 1201/8 468 X 3-Jan 1201 A 24.46 1201/8 468 X 4-Jan 1201 A 25.49 1201/8 468 X 5-Jan 1201 A 26.16 1201/8 468 X 6-Jan 1201 A 25.39 1201/8 468 X 7-Jan 1201 A 25.34 1201/8 468 X 8-Jan 1201 A 25.38 1201/8 468 X 9-Jan 1201 A 24.75 1201/8 468 X 10-Jan 1201 A 24.39 1201/8 468 X 11-Jan 1201 A 25.68 1201/8 468 X 12-Jan 1201 A 25.22 1201/8 468 X 13-Jan 1201 A 25.19 1201/8 468 X 14-Jan 1201 A 26.38 1201/8 468 X 15-Jan 1201 A 25.22 1201/8 468 X 16-Jan 1201 A 24.33 1201/8 468 X 17-Jan 1201 A 24.63 1201/8 468 X 18-Jan 1201 A 25.00 1201/8 468 X 19-Jan 1201 A 25.58 1201/8 468 X 20-Jan 1201 A 25.40 1201/8 468 X 21-Jan 1201 A 25.38 1201/8 468 X 22-Jan 1201 A 25.43 1201/8 468 X 23-Jan 1201 A 24.29 1201/8 468 X 24-Jan 1201 A 24.39 1201/8 468 X 25-Jan 1201 A 26.04 1201/8 468 X 26-Jan 1201 A 25.72 1201/8 468 X 27-Jan 1201 A 26.32 1201/8 468 X 28-Jan 1201 A 25.10 1201/8 468 X 29-Jan 1201 A 26.42 1201/8 468 X 30-Jan 1201 A 24.88 1201/8 468 X 31-Jan 1201 A 24.47 1201/8 468 X 1-Feb 1201 A 25.16 1201/8 468 Y 3-Jan 1201 B 24.05 1201/8 468 Y 4-Jan 1201 B 26.54 1201/8 468 Y 5-Jan 1201 B 25.95 1201/8 468 Y 6-Jan 1201 B 26.62 1201/8 468 Y 7-Jan 1201 B 26.26 1201/8 468 Y 8-Jan 1201 B 26.79 1201/8 468 Y 9-Jan 1201 B 24.07 1201/8 468 Y 10-Jan 1201 B 24.13 1201/8 468 Y 11-Jan 1201 B 26.17 1201/8 468 Y 12-Jan 1201 B 25.58 1201/8 468 Y 13-Jan 1201 B 25.92 1201/8 468 Y 14-Jan 1201 B 25.74 1201/8 468 Y 15-Jan 1201 B 25.32 1201/8 468 Y 16-Jan 1201 B 24.17 1201/8 468 Y 17-Jan 1201 B 24.13 1201/8 468 Y 18-Jan 1201 B 25.18 1201/8 468 Y 19-Jan 1201 B 26.36 1201/8 468 Y 20-Jan 1201 B 26.01 1201/8 468 Y 21-Jan 1201 B 25.83 1201/8 468 Y 22-Jan 1201 B 26.28 1201/8 468 Y 23-Jan 1201 B 24.14 1201/8 468 Y 24-Jan 1201 B 24.26 1201/8 468 Y 25-Jan 1201 B 28.72 1201/8 468 Y 26-Jan 1201 B 26.51 1201/8 468 Y 27-Jan 1201 B 26.82 1201/8 468 Y 28-Jan 1201 B 26.17 1201/8 468 Y 29-Jan 1201 B 26.81 1201/8 468 Y 30-Jan 1201 B 24.08 1201/8 468 Y 31-Jan 1201 B 24.08 1201/8 468 Y 1-Feb 1201 B 25.39 1201/8 468 Z 3-Jan 1201 C 26.35 1201/8 468 Z 4-Jan 1201 C 30.81 1201/8 468 Z 5-Jan 1201 C 29.90 1201/8 468 Z 6-Jan 1201 C 28.09 1201/8 468 Z 7-Jan 1201 C 30.74 1201/8 468 Z 8-Jan 1201 C 29.06 1201/8 468 Z 9-Jan 1201 C 26.74 1201/8 468 Z 10-Jan 1201 C 27.60 1201/8 468 Z 11-Jan 1201 C 29.50 1201/8 468 Z 12-Jan 1201 C 28.72 1201/8 468 Z 13-Jan 1201 C 29.65 1201/8 468 Z 14-Jan 1201 C 28.57 1201/8 468 Z 15-Jan 1201 C 27.90 1201/8 468 Z 16-Jan 1201 C 27.01 1201/8 468 Z 17-Jan 1201 C 27.06 1201/8 468 Z 18-Jan 1201 C 28.57 1201/8 468 Z 19-Jan 1201 C 28.82 1201/8 468 Z 20-Jan 1201 C 29.21 1201/8 468 Z 21-Jan 1201 C 28.71 1201/8 468 Z 22-Jan 1201 C 29.07 1201/8 468 Z 23-Jan 1201 C 27.72 1201/8 468 Z 24-Jan 1201 C 26.92 1201/8 468 Z 25-Jan 1201 C 31.24 1201/8 468 Z 26-Jan 1201 C 30.49 1201/8 468 Z 27-Jan 1201 C 29.54 1201/8 468 Z 28-Jan 1201 C 29.10 1201/8 468 Z 29-Jan 1201 C 31.49 1201/8 468 Z 30-Jan 1201 C 25.69 1201/8 468 Z 31-Jan 1201 C 25.78 1201/8 468 Z 1-Feb 1201 C 29.31 I'd like the output to create a new sheet with three columns: Sector Num, Sector ID, Indices Average. In the example above, my output would be... Sector Num Sector ID Indices Avg 1201 A 26.05 1201 B 26.68 1201 C 30.47 Can anyone help?? It take so much time to do this manually for each report!! Thanks! . |
Sort, Select and Avg Macro
Thanks, Bob! (and Frank)
This works great with one exception. It seems to be averaging the top seven results after the sort. I need to skip the first result and only average lines 2-7 after the sort. So it's only averaging 6 rows, not including the first. I hope that makes sense. Any more thoughts? Thanks!! (Sorry for the delay in response. Busy week!!) "Bob Phillips" wrote: Oops, missed one of the startrows Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 12 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), _ order1:=xlDescending, _ header:=xlNo Next i NextRow = 2 For i = 12 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = _ Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy _ Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = _ Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub Bob "Bob Phillips" wrote in message ... There is other word-wrap Amy which needs correcting. Try this version with continuations t overcome the wrap (and which also starts at row 12 as Frank pointed out Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 2 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), _ order1:=xlDescending, _ header:=xlNo Next i NextRow = 2 For i = 12 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = _ Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy _ Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = _ Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub "Amy" wrote in message ... Thanks, Bob! I got the word wrap fixed up but it keeps erroring out at : Worksheets("Sheet2").Cells(NextRow, "A") I get a "compile error" Expected: = Any thoughts? Amy "Bob Phillips" wrote: Watch the word wrap in the line after the sort Bob "Bob Phillips" wrote in message ... Nicely framed question! My results are a tad different to yours Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 2 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), order1:=xlDescending, header:=xlNo Next i NextRow = 2 For i = 2 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub HTH Bob "Amy" wrote in message ... Please help! I have a report that is pulled regularly. It contains thirty day of data each time. Header row on line 11. Data starts on line 12. Column C & D are the identifiers of a particular sector. Each sector has 30 lines of data, one each for 30 days. Then the next sector starts. For each sector, I need to sort column E from greatest to least, then average the second thru the seventh value. In the data below, 1201A would be the first sector, 1201B is the second...I need to take each sector, sort the Indices greatest to least for that sector, ignore the first line of data after the sort, average the 2-7 lines of data, and ignore the rest of the data for the that sector. Then follow the same process with the next sector. There could be 50 to 100 sectors per report. Data looks like this: System Date Sector Num Sector ID Indices 1201/8 468 X 3-Jan 1201 A 24.46 1201/8 468 X 4-Jan 1201 A 25.49 1201/8 468 X 5-Jan 1201 A 26.16 1201/8 468 X 6-Jan 1201 A 25.39 1201/8 468 X 7-Jan 1201 A 25.34 1201/8 468 X 8-Jan 1201 A 25.38 1201/8 468 X 9-Jan 1201 A 24.75 1201/8 468 X 10-Jan 1201 A 24.39 1201/8 468 X 11-Jan 1201 A 25.68 1201/8 468 X 12-Jan 1201 A 25.22 1201/8 468 X 13-Jan 1201 A 25.19 1201/8 468 X 14-Jan 1201 A 26.38 1201/8 468 X 15-Jan 1201 A 25.22 1201/8 468 X 16-Jan 1201 A 24.33 1201/8 468 X 17-Jan 1201 A 24.63 1201/8 468 X 18-Jan 1201 A 25.00 1201/8 468 X 19-Jan 1201 A 25.58 1201/8 468 X 20-Jan 1201 A 25.40 1201/8 468 X 21-Jan 1201 A 25.38 1201/8 468 X 22-Jan 1201 A 25.43 1201/8 468 X 23-Jan 1201 A 24.29 1201/8 468 X 24-Jan 1201 A 24.39 1201/8 468 X 25-Jan 1201 A 26.04 1201/8 468 X 26-Jan 1201 A 25.72 1201/8 468 X 27-Jan 1201 A 26.32 1201/8 468 X 28-Jan 1201 A 25.10 1201/8 468 X 29-Jan 1201 A 26.42 1201/8 468 X 30-Jan 1201 A 24.88 1201/8 468 X 31-Jan 1201 A 24.47 1201/8 468 X 1-Feb 1201 A 25.16 1201/8 468 Y 3-Jan 1201 B 24.05 1201/8 468 Y 4-Jan 1201 B 26.54 1201/8 468 Y 5-Jan 1201 B 25.95 1201/8 468 Y 6-Jan 1201 B 26.62 1201/8 468 Y 7-Jan 1201 B 26.26 1201/8 468 Y 8-Jan 1201 B 26.79 1201/8 468 Y 9-Jan 1201 B 24.07 1201/8 468 Y 10-Jan 1201 B 24.13 1201/8 468 Y 11-Jan 1201 B 26.17 1201/8 468 Y 12-Jan 1201 B 25.58 1201/8 468 Y 13-Jan 1201 B 25.92 1201/8 468 Y 14-Jan 1201 B 25.74 1201/8 468 Y 15-Jan 1201 B 25.32 1201/8 468 Y 16-Jan 1201 B 24.17 1201/8 468 Y 17-Jan 1201 B 24.13 1201/8 468 Y 18-Jan 1201 B 25.18 1201/8 468 Y 19-Jan 1201 B 26.36 1201/8 468 Y 20-Jan 1201 B 26.01 1201/8 468 Y 21-Jan 1201 B 25.83 1201/8 468 Y 22-Jan 1201 B 26.28 1201/8 468 Y 23-Jan 1201 B 24.14 1201/8 468 Y 24-Jan 1201 B 24.26 1201/8 468 Y 25-Jan 1201 B 28.72 1201/8 468 Y 26-Jan 1201 B 26.51 1201/8 468 Y 27-Jan 1201 B 26.82 1201/8 468 Y 28-Jan 1201 B 26.17 1201/8 468 Y 29-Jan 1201 B 26.81 1201/8 468 Y 30-Jan 1201 B 24.08 1201/8 468 Y 31-Jan 1201 B 24.08 1201/8 468 Y 1-Feb 1201 B 25.39 1201/8 468 Z 3-Jan 1201 C 26.35 1201/8 468 Z 4-Jan 1201 C 30.81 1201/8 468 Z 5-Jan 1201 C 29.90 1201/8 468 Z 6-Jan 1201 C 28.09 1201/8 468 Z 7-Jan 1201 C 30.74 1201/8 468 Z 8-Jan 1201 C 29.06 1201/8 468 Z 9-Jan 1201 C 26.74 1201/8 468 Z 10-Jan 1201 C 27.60 1201/8 468 Z 11-Jan 1201 C 29.50 1201/8 468 Z 12-Jan 1201 C 28.72 1201/8 468 Z 13-Jan 1201 C 29.65 1201/8 468 Z 14-Jan 1201 C 28.57 1201/8 468 Z 15-Jan 1201 C 27.90 1201/8 468 Z 16-Jan 1201 C 27.01 1201/8 468 Z 17-Jan 1201 C 27.06 1201/8 468 Z 18-Jan 1201 C 28.57 1201/8 468 Z 19-Jan 1201 C 28.82 1201/8 468 Z 20-Jan 1201 C 29.21 1201/8 468 Z 21-Jan 1201 C 28.71 1201/8 468 Z 22-Jan 1201 C 29.07 1201/8 468 Z 23-Jan 1201 C 27.72 1201/8 468 Z 24-Jan 1201 C 26.92 1201/8 468 Z 25-Jan 1201 C 31.24 1201/8 468 Z 26-Jan 1201 C 30.49 1201/8 468 Z 27-Jan 1201 C 29.54 1201/8 468 Z 28-Jan 1201 C 29.10 1201/8 468 Z 29-Jan 1201 C 31.49 1201/8 468 Z 30-Jan 1201 C 25.69 1201/8 468 Z 31-Jan 1201 C 25.78 1201/8 468 Z 1-Feb 1201 C 29.31 I'd like the output to create a new sheet with three columns: Sector Num, Sector ID, Indices Average. In the example above, my output would be... Sector Num Sector ID Indices Avg 1201 A 26.05 1201 B 26.68 1201 C 30.47 Can anyone help?? It take so much time to do this manually for each report!! Thanks! . . |
Sort, Select and Avg Macro
Try this
Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For i = 12 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), _ order1:=xlDescending, _ header:=xlNo Next i NextRow = 2 For i = 12 To LastRow Step 30 Worksheets("Sheet2").Range("A1:C1") = _ Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy _ Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = _ Application.Average(.Cells(i, "E").Resize(6)) NextRow = NextRow + 1 Next i End With End Sub HTH Bob "Amy" wrote in message ... Thanks, Bob! (and Frank) This works great with one exception. It seems to be averaging the top seven results after the sort. I need to skip the first result and only average lines 2-7 after the sort. So it's only averaging 6 rows, not including the first. I hope that makes sense. Any more thoughts? Thanks!! (Sorry for the delay in response. Busy week!!) "Bob Phillips" wrote: Oops, missed one of the startrows Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 12 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), _ order1:=xlDescending, _ header:=xlNo Next i NextRow = 2 For i = 12 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = _ Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy _ Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = _ Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub Bob "Bob Phillips" wrote in message ... There is other word-wrap Amy which needs correcting. Try this version with continuations t overcome the wrap (and which also starts at row 12 as Frank pointed out Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 2 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), _ order1:=xlDescending, _ header:=xlNo Next i NextRow = 2 For i = 12 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = _ Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy _ Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = _ Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub "Amy" wrote in message ... Thanks, Bob! I got the word wrap fixed up but it keeps erroring out at : Worksheets("Sheet2").Cells(NextRow, "A") I get a "compile error" Expected: = Any thoughts? Amy "Bob Phillips" wrote: Watch the word wrap in the line after the sort Bob "Bob Phillips" wrote in message ... Nicely framed question! My results are a tad different to yours Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).row For i = 2 To LastRow Step 30 .Cells(i, "A").Resize(30, 5).Sort _ key1:=.Cells(i, "E"), order1:=xlDescending, header:=xlNo Next i NextRow = 2 For i = 2 To LastRow Step 30 Worksheets("Sheet2").Range("a1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy Worksheets("Sheet2").Cells(NextRow, "A") Worksheets("Sheet2").Cells(NextRow, "C").Value = Application.Average(.Cells(i, "E").Resize(7)) NextRow = NextRow + 1 Next i End With End Sub HTH Bob "Amy" wrote in message ... Please help! I have a report that is pulled regularly. It contains thirty day of data each time. Header row on line 11. Data starts on line 12. Column C & D are the identifiers of a particular sector. Each sector has 30 lines of data, one each for 30 days. Then the next sector starts. For each sector, I need to sort column E from greatest to least, then average the second thru the seventh value. In the data below, 1201A would be the first sector, 1201B is the second...I need to take each sector, sort the Indices greatest to least for that sector, ignore the first line of data after the sort, average the 2-7 lines of data, and ignore the rest of the data for the that sector. Then follow the same process with the next sector. There could be 50 to 100 sectors per report. Data looks like this: System Date Sector Num Sector ID Indices 1201/8 468 X 3-Jan 1201 A 24.46 1201/8 468 X 4-Jan 1201 A 25.49 1201/8 468 X 5-Jan 1201 A 26.16 1201/8 468 X 6-Jan 1201 A 25.39 1201/8 468 X 7-Jan 1201 A 25.34 1201/8 468 X 8-Jan 1201 A 25.38 1201/8 468 X 9-Jan 1201 A 24.75 1201/8 468 X 10-Jan 1201 A 24.39 1201/8 468 X 11-Jan 1201 A 25.68 1201/8 468 X 12-Jan 1201 A 25.22 1201/8 468 X 13-Jan 1201 A 25.19 1201/8 468 X 14-Jan 1201 A 26.38 1201/8 468 X 15-Jan 1201 A 25.22 1201/8 468 X 16-Jan 1201 A 24.33 1201/8 468 X 17-Jan 1201 A 24.63 1201/8 468 X 18-Jan 1201 A 25.00 1201/8 468 X 19-Jan 1201 A 25.58 1201/8 468 X 20-Jan 1201 A 25.40 1201/8 468 X 21-Jan 1201 A 25.38 1201/8 468 X 22-Jan 1201 A 25.43 1201/8 468 X 23-Jan 1201 A 24.29 1201/8 468 X 24-Jan 1201 A 24.39 1201/8 468 X 25-Jan 1201 A 26.04 1201/8 468 X 26-Jan 1201 A 25.72 1201/8 468 X 27-Jan 1201 A 26.32 1201/8 468 X 28-Jan 1201 A 25.10 1201/8 468 X 29-Jan 1201 A 26.42 1201/8 468 X 30-Jan 1201 A 24.88 1201/8 468 X 31-Jan 1201 A 24.47 1201/8 468 X 1-Feb 1201 A 25.16 1201/8 468 Y 3-Jan 1201 B 24.05 1201/8 468 Y 4-Jan 1201 B 26.54 1201/8 468 Y 5-Jan 1201 B 25.95 1201/8 468 Y 6-Jan 1201 B 26.62 1201/8 468 Y 7-Jan 1201 B 26.26 1201/8 468 Y 8-Jan 1201 B 26.79 1201/8 468 Y 9-Jan 1201 B 24.07 1201/8 468 Y 10-Jan 1201 B 24.13 1201/8 468 Y 11-Jan 1201 B 26.17 1201/8 468 Y 12-Jan 1201 B 25.58 1201/8 468 Y 13-Jan 1201 B 25.92 1201/8 468 Y 14-Jan 1201 B 25.74 1201/8 468 Y 15-Jan 1201 B 25.32 1201/8 468 Y 16-Jan 1201 B 24.17 1201/8 468 Y 17-Jan 1201 B 24.13 1201/8 468 Y 18-Jan 1201 B 25.18 1201/8 468 Y 19-Jan 1201 B 26.36 1201/8 468 Y 20-Jan 1201 B 26.01 1201/8 468 Y 21-Jan 1201 B 25.83 1201/8 468 Y 22-Jan 1201 B 26.28 1201/8 468 Y 23-Jan 1201 B 24.14 1201/8 468 Y 24-Jan 1201 B 24.26 1201/8 468 Y 25-Jan 1201 B 28.72 1201/8 468 Y 26-Jan 1201 B 26.51 1201/8 468 Y 27-Jan 1201 B 26.82 1201/8 468 Y 28-Jan 1201 B 26.17 1201/8 468 Y 29-Jan 1201 B 26.81 1201/8 468 Y 30-Jan 1201 B 24.08 1201/8 468 Y 31-Jan 1201 B 24.08 1201/8 468 Y 1-Feb 1201 B 25.39 1201/8 468 Z 3-Jan 1201 C 26.35 1201/8 468 Z 4-Jan 1201 C 30.81 1201/8 468 Z 5-Jan 1201 C 29.90 1201/8 468 Z 6-Jan 1201 C 28.09 1201/8 468 Z 7-Jan 1201 C 30.74 1201/8 468 Z 8-Jan 1201 C 29.06 1201/8 468 Z 9-Jan 1201 C 26.74 1201/8 468 Z 10-Jan 1201 C 27.60 1201/8 468 Z 11-Jan 1201 C 29.50 1201/8 468 Z 12-Jan 1201 C 28.72 1201/8 468 Z 13-Jan 1201 C 29.65 1201/8 468 Z 14-Jan 1201 C 28.57 1201/8 468 Z 15-Jan 1201 C 27.90 1201/8 468 Z 16-Jan 1201 C 27.01 1201/8 468 Z 17-Jan 1201 C 27.06 1201/8 468 Z 18-Jan 1201 C 28.57 1201/8 468 Z 19-Jan 1201 C 28.82 1201/8 468 Z 20-Jan 1201 C 29.21 1201/8 468 Z 21-Jan 1201 C 28.71 1201/8 468 Z 22-Jan 1201 C 29.07 1201/8 468 Z 23-Jan 1201 C 27.72 1201/8 468 Z 24-Jan 1201 C 26.92 1201/8 468 Z 25-Jan 1201 C 31.24 1201/8 468 Z 26-Jan 1201 C 30.49 1201/8 468 Z 27-Jan 1201 C 29.54 1201/8 468 Z 28-Jan 1201 C 29.10 1201/8 468 Z 29-Jan 1201 C 31.49 1201/8 468 Z 30-Jan 1201 C 25.69 1201/8 468 Z 31-Jan 1201 C 25.78 1201/8 468 Z 1-Feb 1201 C 29.31 I'd like the output to create a new sheet with three columns: Sector Num, Sector ID, Indices Average. In the example above, my output would be... Sector Num Sector ID Indices Avg 1201 A 26.05 1201 B 26.68 1201 C 30.47 Can anyone help?? It take so much time to do this manually for each report!! Thanks! . . |
| All times are GMT +1. The time now is 01:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com