| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort, select and average macro help
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort, select and average macro help
No need for a macro or anythying complicated. Take a look at pivot tables...
http://peltiertech.com/Excel/Pivots/pivotstart.htm In your case. Select the Data area. Select Data | Pivot Talbe or Pivot Chart... When the wizard opens just select Finish A pivot table will be created for you on a new sheet. From the fields List Drag your Sector Num, Sector ID into the left hand column Drag the Indices Avg into the center data section. Right click on oany of the values in the data section and select Field Settings. Change the aggregation from Sum to Average. -- HTH... Jim Thomlinson "Amy" wrote: 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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort, select and average macro help
A pivot table doesn't work for me because I don't need an average of all the
values. I only need the average of the highest 6 indices not including the first highest. "Jim Thomlinson" wrote: No need for a macro or anythying complicated. Take a look at pivot tables... http://peltiertech.com/Excel/Pivots/pivotstart.htm In your case. Select the Data area. Select Data | Pivot Talbe or Pivot Chart... When the wizard opens just select Finish A pivot table will be created for you on a new sheet. From the fields List Drag your Sector Num, Sector ID into the left hand column Drag the Indices Avg into the center data section. Right click on oany of the values in the data section and select Field Settings. Change the aggregation from Sum to Average. -- HTH... Jim Thomlinson "Amy" wrote: 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to select cells without a certain value and select a menu it | Excel Worksheet Functions | |||
| Average of select range of numbers | Excel Worksheet Functions | |||
| select date range then find average of values in another cell | Excel Worksheet Functions | |||
| Need to select rows to average based on a value in a different column | New Users to Excel | |||
| Select rows, then sort | Excel Discussion (Misc queries) | |||
Linear Mode
