I am using excel 2003

I have a file like below
Rate month GBP
1.46 FEB 2000
1.47 MAR 3000
1.47 APR 3500
1.48 MAY 1000

I want to create summary
Rate FEB MAR APR MAY
1.46 2000
1.47 3000 3500
1.48 1000

I can use sumif function to return the total, however, how can I insert only
the unique rate in the first column automatically.

Thanks a lot !
eva cheng

A pivot tabel will work for that... Select Data | Pivot table or Pivot
Chart... | Follow the wizard. Put the rate in the left column and the months
in the top row. Add the GBP to the data area and you are done... no formulas
needed...
--
HTH...

Jim Thomlinson


"eva cheng" wrote:

I am using excel 2003

I have a file like below
Rate month GBP
1.46 FEB 2000
1.47 MAR 3000
1.47 APR 3500
1.48 MAY 1000

I want to create summary
Rate FEB MAR APR MAY
1.46 2000
1.47 3000 3500
1.48 1000

I can use sumif function to return the total, however, how can I insert only
the unique rate in the first column automatically.

Thanks a lot !
eva cheng

Hi,
I suppose information is in sheet 1 columns A,B, C and you want the output
in sheet2
=index(sheet1!$C$2:$C$1000,match(A1,sheet1!$A$2:$A $1000,0),match(B1,sheet1!$B$1:$B$1000))



"eva cheng" wrote:

I am using excel 2003

I have a file like below
Rate month GBP
1.46 FEB 2000
1.47 MAR 3000
1.47 APR 3500
1.48 MAY 1000

I want to create summary
Rate FEB MAR APR MAY
1.46 2000
1.47 3000 3500
1.48 1000

I can use sumif function to return the total, however, how can I insert only
the unique rate in the first column automatically.

Thanks a lot !
eva cheng

On Fri, 7 May 2010 09:31:01 -0700, eva cheng
wrote:

I am using excel 2003

I have a file like below
Rate month GBP
1.46 FEB 2000
1.47 MAR 3000
1.47 APR 3500
1.48 MAY 1000

I want to create summary
Rate FEB MAR APR MAY
1.46 2000
1.47 3000 3500
1.48 1000

I can use sumif function to return the total, however, how can I insert only
the unique rate in the first column automatically.

Thanks a lot !
eva cheng

Pretty good setup for a Pivot Table.

I don't recall where it is on the 2003 menu -- possibly the Data or Tools
menus.

Once you find it: Insert/Pivot Table

Drag Rate to the Rows area.
Drag Month to the Columns area
Drag GBP to the Data or Values area

Format to taste.
--ron