Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Sort, Select and Avg Macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Amy Amy is offline
external usenet poster
  
Posts: 165
Default 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!


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 834
Default 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!




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 834
Default 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!






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
Amy Amy is offline
external usenet poster
  
Posts: 165
Default 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!






.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default 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!






.

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 834
Default 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!






.




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 834
Default 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!






.






Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
Amy Amy is offline
external usenet poster
  
Posts: 165
Default 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!






.






.

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 834
Default 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!






.






.



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort, select and average macro help Amy Excel Discussion (Misc queries) 2 February 5th 10 07:01 PM
create Macro €“ select data, sort by acc no., yr, part no, create P Johnny Excel Programming 0 November 22nd 06 03:18 PM
Sort method possible without using select WhytheQ Excel Programming 2 August 11th 06 06:57 PM
Select rows, then sort David Excel Discussion (Misc queries) 2 April 30th 05 04:36 AM
Select and then sort a list Ian_Limbo[_2_] Excel Programming 8 January 6th 05 03:15 PM


All times are GMT +1. The time now is 02:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement