Pivot Table? ID, Date, Value sorting

[Mark]

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

 

[ShaneDevenshire]

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.

 

[ShaneDevenshire]

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.

 

[Mark]

Many thanks, I'll give it a go

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.

 

[Mark]

Many thanks for your effort Shane

Works a treat. Your help is much appreciated.

Good luck
Mark