Pivot Table : First function ???

[Kevin]

I have the following sample data.

Machine Date Title
MC1 1/4 T1
MC2 1/4 T2
MC1 2/4 T3
MC3 2/4 T4

Now I'd like to generate a sheet as follows:
Machine Date
1/4 2/4
MC1 T1 T3
MC2 T2
MC3 T4

I try to use the pivot table to generate the sheet but I
cannot use the First or Last function (as in MS Access).
Does anyone have any tricks to create the required sheet
without using MS Access ?


Many thanks,
Kevin

 

[Guest]

You can use the INDEX and MATCH functions to create your summary table. With your sample data in cells A1:C5,
Enter the machine names in cells F2:F4.
Enter the dates in cells G1:H1
Enter the following formula in cell G2
=INDEX($C$2:$C$5,MATCH($F2&G$1,$A$2:$A$5&$B$2:$B$5,0)
This is an array formula -- after you type the formula, hold the Shift and Ctrl keys, and press Enter (Ctrl+Shift+Enter
Copy the formula across to column H, and down to row 4

--
Debra Dalgleis
Excel FAQ, Tips & Book Lis
http://www.contextures.com/tiptech.htm

----- Kevin wrote: ----

I have the following sample data

Machine Date Titl
MC1 1/4 T
MC2 1/4 T
MC1 2/4 T
MC3 2/4 T

Now I'd like to generate a sheet as follows
Machine Dat
1/4 2/
MC1 T1 T
MC2 T
MC3 T

I try to use the pivot table to generate the sheet but I
cannot use the First or Last function (as in MS Access).
Does anyone have any tricks to create the required sheet
without using MS Access


Many thanks
Kevi

 

[Kevin]

Many thanks.