| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging specific rows - HELP!
Hi. My original post was several weeks ago and Ive received a lot of help
but Im not quite there. Below is my original post. Ive also posted the code Im working with now. This works great with two exceptions. First, 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 of every sector after the sort. So it's only averaging 6 rows per sector, not including the first row. I hope that makes sense. Secondly, I need the code to create Sheet2 because there isnt one in the file to begin with. Any suggestions would be great! Thanks! ORIGINAL POST: 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! CODE: 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Averaging specific rows - HELP!
Give this a try. Hope this helps! If so, let me know, click "YES" below.
Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long Dim wks2 As Worksheet 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 ' create new worksheet Set wks2 = Worksheets.Add(After:=Sheets("Sheet1")) wks2.Name = "Sheet2" NextRow = 2 For i = 12 To LastRow Step 30 wks2.Range("A1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy wks2.Cells(NextRow, "A") wks2.Cells(NextRow, "C").Value = Application.Average(.Cells(i + 1, "E").Resize(6)) NextRow = NextRow + 1 Next i End With End Sub -- Cheers, Ryan "Amy" wrote: Hi. My original post was several weeks ago and Ive received a lot of help but Im not quite there. Below is my original post. Ive also posted the code Im working with now. This works great with two exceptions. First, 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 of every sector after the sort. So it's only averaging 6 rows per sector, not including the first row. I hope that makes sense. Secondly, I need the code to create Sheet2 because there isnt one in the file to begin with. Any suggestions would be great! Thanks! ORIGINAL POST: 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! CODE: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Averaging specific rows - HELP!
Ryan,
This works great! Thanks!! A couple of things I'd like to adjust. Can the code be adjusted to run on the current sheet rather than "Sheet1" The sheet has a different name every time I run the report. Also, can "Sheet2" be automatically named after the current sheet plus the word "Avgs"? So if current sheet is "Sectors_02022010", the new sheet would be named "Sectors_02022010 Avgs". Thanks sooooo much for your help! This is going to save a ton of time!! Amy "Ryan H" wrote: Give this a try. Hope this helps! If so, let me know, click "YES" below. Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long Dim wks2 As Worksheet 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 ' create new worksheet Set wks2 = Worksheets.Add(After:=Sheets("Sheet1")) wks2.Name = "Sheet2" NextRow = 2 For i = 12 To LastRow Step 30 wks2.Range("A1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy wks2.Cells(NextRow, "A") wks2.Cells(NextRow, "C").Value = Application.Average(.Cells(i + 1, "E").Resize(6)) NextRow = NextRow + 1 Next i End With End Sub -- Cheers, Ryan "Amy" wrote: Hi. My original post was several weeks ago and Ive received a lot of help but Im not quite there. Below is my original post. Ive also posted the code Im working with now. This works great with two exceptions. First, 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 of every sector after the sort. So it's only averaging 6 rows per sector, not including the first row. I hope that makes sense. Secondly, I need the code to create Sheet2 because there isnt one in the file to begin with. Any suggestions would be great! Thanks! ORIGINAL POST: 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! CODE: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Averaging specific rows - HELP!
Ryan,
This is great! Works exactly like I was hoping! Just a couple more things I'd like to change if possible. Can it be set up to run off of the current sheet rather than "Sheet1". The sheet name changes everytime I run the report. Also, can "Sheet2" be named differently? I'd like it to be named based on the current sheet if possible plus the word "Avgs". So if the current sheet is "Sectors_02152010", the new sheet would be "Sectors_02152010 Avgs". This is so helpful! Going to save tons of time!! Thanks!! Amy "Ryan H" wrote: Give this a try. Hope this helps! If so, let me know, click "YES" below. Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long Dim wks2 As Worksheet 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 ' create new worksheet Set wks2 = Worksheets.Add(After:=Sheets("Sheet1")) wks2.Name = "Sheet2" NextRow = 2 For i = 12 To LastRow Step 30 wks2.Range("A1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy wks2.Cells(NextRow, "A") wks2.Cells(NextRow, "C").Value = Application.Average(.Cells(i + 1, "E").Resize(6)) NextRow = NextRow + 1 Next i End With End Sub -- Cheers, Ryan "Amy" wrote: Hi. My original post was several weeks ago and Ive received a lot of help but Im not quite there. Below is my original post. Ive also posted the code Im working with now. This works great with two exceptions. First, 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 of every sector after the sort. So it's only averaging 6 rows per sector, not including the first row. I hope that makes sense. Secondly, I need the code to create Sheet2 because there isnt one in the file to begin with. Any suggestions would be great! Thanks! ORIGINAL POST: 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! CODE: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Averaging specific rows - HELP!
You own me lunch if this works! lol. Give this code a try. Hope this
helps! If so, click "YES" below. Public Sub ProcessData() Dim wks1 As Worksheet Dim wks2 As Worksheet Dim i As Long Dim LastRow As Long Dim NextRow As Long Set wks1 = ActiveSheet LastRow = wks1.Cells(Rows.Count, "C").End(xlUp).Row For i = 12 To LastRow Step 30 wks1.Cells(i, "A").Resize(30, 5).Sort _ key1:=wks1.Cells(i, "E"), _ order1:=xlDescending, _ Header:=xlNo Next i ' create new worksheet Set wks2 = Worksheets.Add(After:=wks1) wks2.Name = wks1.Name & " Avgs" NextRow = 2 For i = 12 To LastRow Step 30 wks2.Range("A1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") wks1.Cells(i, "C").Resize(, 2).Copy wks2.Cells(NextRow, "A") wks2.Cells(NextRow, "C").Value = Application.Average(wks1.Cells(i + 1, "E").Resize(6)) NextRow = NextRow + 1 Next i End Sub -- Cheers, Ryan "Amy" wrote: Ryan, This is great! Works exactly like I was hoping! Just a couple more things I'd like to change if possible. Can it be set up to run off of the current sheet rather than "Sheet1". The sheet name changes everytime I run the report. Also, can "Sheet2" be named differently? I'd like it to be named based on the current sheet if possible plus the word "Avgs". So if the current sheet is "Sectors_02152010", the new sheet would be "Sectors_02152010 Avgs". This is so helpful! Going to save tons of time!! Thanks!! Amy "Ryan H" wrote: Give this a try. Hope this helps! If so, let me know, click "YES" below. Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long Dim wks2 As Worksheet 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 ' create new worksheet Set wks2 = Worksheets.Add(After:=Sheets("Sheet1")) wks2.Name = "Sheet2" NextRow = 2 For i = 12 To LastRow Step 30 wks2.Range("A1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy wks2.Cells(NextRow, "A") wks2.Cells(NextRow, "C").Value = Application.Average(.Cells(i + 1, "E").Resize(6)) NextRow = NextRow + 1 Next i End With End Sub -- Cheers, Ryan "Amy" wrote: Hi. My original post was several weeks ago and Ive received a lot of help but Im not quite there. Below is my original post. Ive also posted the code Im working with now. This works great with two exceptions. First, 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 of every sector after the sort. So it's only averaging 6 rows per sector, not including the first row. I hope that makes sense. Secondly, I need the code to create Sheet2 because there isnt one in the file to begin with. Any suggestions would be great! Thanks! ORIGINAL POST: 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! CODE: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Averaging specific rows - HELP!
It works!! My new favorite hero! Thanks again!!
Amy "Ryan H" wrote: You own me lunch if this works! lol. Give this code a try. Hope this helps! If so, click "YES" below. Public Sub ProcessData() Dim wks1 As Worksheet Dim wks2 As Worksheet Dim i As Long Dim LastRow As Long Dim NextRow As Long Set wks1 = ActiveSheet LastRow = wks1.Cells(Rows.Count, "C").End(xlUp).Row For i = 12 To LastRow Step 30 wks1.Cells(i, "A").Resize(30, 5).Sort _ key1:=wks1.Cells(i, "E"), _ order1:=xlDescending, _ Header:=xlNo Next i ' create new worksheet Set wks2 = Worksheets.Add(After:=wks1) wks2.Name = wks1.Name & " Avgs" NextRow = 2 For i = 12 To LastRow Step 30 wks2.Range("A1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") wks1.Cells(i, "C").Resize(, 2).Copy wks2.Cells(NextRow, "A") wks2.Cells(NextRow, "C").Value = Application.Average(wks1.Cells(i + 1, "E").Resize(6)) NextRow = NextRow + 1 Next i End Sub -- Cheers, Ryan "Amy" wrote: Ryan, This is great! Works exactly like I was hoping! Just a couple more things I'd like to change if possible. Can it be set up to run off of the current sheet rather than "Sheet1". The sheet name changes everytime I run the report. Also, can "Sheet2" be named differently? I'd like it to be named based on the current sheet if possible plus the word "Avgs". So if the current sheet is "Sectors_02152010", the new sheet would be "Sectors_02152010 Avgs". This is so helpful! Going to save tons of time!! Thanks!! Amy "Ryan H" wrote: Give this a try. Hope this helps! If so, let me know, click "YES" below. Public Sub ProcessData() Dim i As Long Dim LastRow As Long Dim NextRow As Long Dim wks2 As Worksheet 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 ' create new worksheet Set wks2 = Worksheets.Add(After:=Sheets("Sheet1")) wks2.Name = "Sheet2" NextRow = 2 For i = 12 To LastRow Step 30 wks2.Range("A1:C1") = Array("Sector Num", "Sector ID", "Indices Avg") .Cells(i, "C").Resize(, 2).Copy wks2.Cells(NextRow, "A") wks2.Cells(NextRow, "C").Value = Application.Average(.Cells(i + 1, "E").Resize(6)) NextRow = NextRow + 1 Next i End With End Sub -- Cheers, Ryan "Amy" wrote: Hi. My original post was several weeks ago and Ive received a lot of help but Im not quite there. Below is my original post. Ive also posted the code Im working with now. This works great with two exceptions. First, 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 of every sector after the sort. So it's only averaging 6 rows per sector, not including the first row. I hope that makes sense. Secondly, I need the code to create Sheet2 because there isnt one in the file to begin with. Any suggestions would be great! Thanks! ORIGINAL POST: 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! CODE: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need quick help averaging multiple rows | Excel Worksheet Functions | |||
| A specific method for conditional averaging? | Excel Worksheet Functions | |||
| Deleting specific rows with a specific criteria using inputbox | Excel Programming | |||
| averaging specific rows in multiple arrays | Excel Worksheet Functions | |||
| Averaging specific data in columns | Excel Programming | |||
Linear Mode
