| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table? ID, Date, Value sorting
Hi
Excel 03 I'm wondering if a Pivot Table is the way to go. I have data in this format ID, Date, Value1, Value2, Value3, Value4, Value5 Each ID has Values by different dates usually a maximum of 5 dates. I want the most recent Value by date for each ID from each column - Value1, Value2, Value3 etc For example, for ID 02 below would be as at 0712: ID Date V1 V2 V3 V4 V5 02 0712 40 20 30 05 10 Now I need to extract this data to a spreadsheet to import into another application Many thanks Mark EG of data (BTW Values are percentages) ID Date V1 V2 V3 V4 V5 01 0612 12 22 45 01 0712 14 40 22 50 02 0610 50 15 20 10 05 02 0612 45 30 05 10 02 0712 40 20 30 10 03 ... 03 ... 03... 03 ... 04... 04... 05... 05... 05... etc to 600 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table? ID, Date, Value sorting
Hi,
I am not sure the "best" way to go is a pivot table but it will work: 1. Highlight your data and choose Data, PivotTable and PivotChart Report, Next, Next, Layout. 2. Put the ID field in the Row area and put the Date field below it in the Row area, and then put the V1, V2, V3,... V5 fields in the Data area, and put the Date field in the Data area. (Yes put the Date field in two locations) Click OK, Finish. 3. The V1.. fields will be laid out vertically and the work Total will be in the top right corner of the pivot table. Drag the Data button to the cell with the word Total. This will lay the fields out horizontally. 4. Click on a cell in any field that has made a Count calculation instead of Sum and choose the Field Setting button on the PivotTable toolbar. Set the Summarize by to Sum. Do this for all V fields but for the Date field choose the Max summary operation. 5. Double-click the Date button in the Row area and click Advanced. Turn on Top 10 AutoShow and set the number to show to 1. Then change the Using Field to Max of Date and click OK twice. 6. Select the whole pivot table and copy it. Move to an empty area and choose Edit, Paste Special, Values. 7. Clean up the titles, remove grand totals, get rid of the Max of Date column. If I missed something let me know. -- Cheers, Shane Devenshire "Mark" wrote: Hi Excel 03 I'm wondering if a Pivot Table is the way to go. I have data in this format ID, Date, Value1, Value2, Value3, Value4, Value5 Each ID has Values by different dates usually a maximum of 5 dates. I want the most recent Value by date for each ID from each column - Value1, Value2, Value3 etc For example, for ID 02 below would be as at 0712: ID Date V1 V2 V3 V4 V5 02 0712 40 20 30 05 10 Now I need to extract this data to a spreadsheet to import into another application Many thanks Mark EG of data (BTW Values are percentages) ID Date V1 V2 V3 V4 V5 01 0612 12 22 45 01 0712 14 40 22 50 02 0610 50 15 20 10 05 02 0612 45 30 05 10 02 0712 40 20 30 10 03 ... 03 ... 03... 03 ... 04... 04... 05... 05... 05... etc to 600 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table? ID, Date, Value sorting
Many thanks, I'll give it a go
"ShaneDevenshire" wrote in message ... Hi, I am not sure the "best" way to go is a pivot table but it will work: 1. Highlight your data and choose Data, PivotTable and PivotChart Report, Next, Next, Layout. 2. Put the ID field in the Row area and put the Date field below it in the Row area, and then put the V1, V2, V3,... V5 fields in the Data area, and put the Date field in the Data area. (Yes put the Date field in two locations) Click OK, Finish. 3. The V1.. fields will be laid out vertically and the work Total will be in the top right corner of the pivot table. Drag the Data button to the cell with the word Total. This will lay the fields out horizontally. 4. Click on a cell in any field that has made a Count calculation instead of Sum and choose the Field Setting button on the PivotTable toolbar. Set the Summarize by to Sum. Do this for all V fields but for the Date field choose the Max summary operation. 5. Double-click the Date button in the Row area and click Advanced. Turn on Top 10 AutoShow and set the number to show to 1. Then change the Using Field to Max of Date and click OK twice. 6. Select the whole pivot table and copy it. Move to an empty area and choose Edit, Paste Special, Values. 7. Clean up the titles, remove grand totals, get rid of the Max of Date column. If I missed something let me know. -- Cheers, Shane Devenshire "Mark" wrote: Hi Excel 03 I'm wondering if a Pivot Table is the way to go. I have data in this format ID, Date, Value1, Value2, Value3, Value4, Value5 Each ID has Values by different dates usually a maximum of 5 dates. I want the most recent Value by date for each ID from each column - Value1, Value2, Value3 etc For example, for ID 02 below would be as at 0712: ID Date V1 V2 V3 V4 V5 02 0712 40 20 30 05 10 Now I need to extract this data to a spreadsheet to import into another application Many thanks Mark EG of data (BTW Values are percentages) ID Date V1 V2 V3 V4 V5 01 0612 12 22 45 01 0712 14 40 22 50 02 0610 50 15 20 10 05 02 0612 45 30 05 10 02 0712 40 20 30 10 03 ... 03 ... 03... 03 ... 04... 04... 05... 05... 05... etc to 600 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table? ID, Date, Value sorting
Hi,
I am not sure the "best" way to go is a pivot table but it will work: 1. Highlight your data and choose Data, PivotTable and PivotChart Report, Next, Next, Layout. 2. Put the ID field in the Row area and put the Date field below it in the Row area, and then put the V1, V2, V3,... V5 fields in the Data area, and put the Date field in the Data area. (Yes put the Date field in two locations) Click OK, Finish. 3. The V1.. fields will be laid out vertically and the work Total will be in the top right corner of the pivot table. Drag the Data button to the cell with the word Total. This will lay the fields out horizontally. 4. Click on a cell in any field that has made a Count calculation instead of Sum and choose the Field Setting button on the PivotTable toolbar. Set the Summarize by to Sum. Do this for all V fields but for the Date field choose the Max summary operation. 5. Double-click the Date button in the Row area and click Advanced. Turn on Top 10 AutoShow and set the number to show to 1. Then change the Using Field to Max of Date and click OK twice. 6. Select the whole pivot table and copy it. Move to an empty area and choose Edit, Paste Special, Values. 7. Clean up the titles, remove grand totals, get rid of the Max of Date column. If I missed something let me know. -- Cheers, Shane Devenshire "Mark" wrote: Hi Excel 03 I'm wondering if a Pivot Table is the way to go. I have data in this format ID, Date, Value1, Value2, Value3, Value4, Value5 Each ID has Values by different dates usually a maximum of 5 dates. I want the most recent Value by date for each ID from each column - Value1, Value2, Value3 etc For example, for ID 02 below would be as at 0712: ID Date V1 V2 V3 V4 V5 02 0712 40 20 30 05 10 Now I need to extract this data to a spreadsheet to import into another application Many thanks Mark EG of data (BTW Values are percentages) ID Date V1 V2 V3 V4 V5 01 0612 12 22 45 01 0712 14 40 22 50 02 0610 50 15 20 10 05 02 0612 45 30 05 10 02 0712 40 20 30 10 03 ... 03 ... 03... 03 ... 04... 04... 05... 05... 05... etc to 600 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Table? ID, Date, Value sorting
Many thanks for your effort Shane
Works a treat. Your help is much appreciated. Good luck Mark "ShaneDevenshire" wrote in message ... Hi, I am not sure the "best" way to go is a pivot table but it will work: 1. Highlight your data and choose Data, PivotTable and PivotChart Report, Next, Next, Layout. 2. Put the ID field in the Row area and put the Date field below it in the Row area, and then put the V1, V2, V3,... V5 fields in the Data area, and put the Date field in the Data area. (Yes put the Date field in two locations) Click OK, Finish. 3. The V1.. fields will be laid out vertically and the work Total will be in the top right corner of the pivot table. Drag the Data button to the cell with the word Total. This will lay the fields out horizontally. 4. Click on a cell in any field that has made a Count calculation instead of Sum and choose the Field Setting button on the PivotTable toolbar. Set the Summarize by to Sum. Do this for all V fields but for the Date field choose the Max summary operation. 5. Double-click the Date button in the Row area and click Advanced. Turn on Top 10 AutoShow and set the number to show to 1. Then change the Using Field to Max of Date and click OK twice. 6. Select the whole pivot table and copy it. Move to an empty area and choose Edit, Paste Special, Values. 7. Clean up the titles, remove grand totals, get rid of the Max of Date column. If I missed something let me know. -- Cheers, Shane Devenshire "Mark" wrote: Hi Excel 03 I'm wondering if a Pivot Table is the way to go. I have data in this format ID, Date, Value1, Value2, Value3, Value4, Value5 Each ID has Values by different dates usually a maximum of 5 dates. I want the most recent Value by date for each ID from each column - Value1, Value2, Value3 etc For example, for ID 02 below would be as at 0712: ID Date V1 V2 V3 V4 V5 02 0712 40 20 30 05 10 Now I need to extract this data to a spreadsheet to import into another application Many thanks Mark EG of data (BTW Values are percentages) ID Date V1 V2 V3 V4 V5 01 0612 12 22 45 01 0712 14 40 22 50 02 0610 50 15 20 10 05 02 0612 45 30 05 10 02 0712 40 20 30 10 03 ... 03 ... 03... 03 ... 04... 04... 05... 05... 05... etc to 600 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot Table Sorting | Excel Worksheet Functions | |||
| Pivot Table Sorting | Excel Worksheet Functions | |||
| PIVOT Table Sorting - PLEASE Help!!!!! | Excel Discussion (Misc queries) | |||
| pivot table sorting | Excel Worksheet Functions | |||
| Pivot Table sorting | Excel Discussion (Misc queries) | |||
![Mark[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
