A
Amy
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!
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!