Prev: email as pdf print area excel 2007
Next: sumif color cells with same month
From: eva cheng on 7 May 2010 12:31
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
From: Jim Thomlinson on 7 May 2010 13:16
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
From: Eduardo on 7 May 2010 13:16
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
From: Ron Rosenfeld on 7 May 2010 13:19
On Fri, 7 May 2010 09:31:01 -0700, eva cheng
<evacheng(a)discussions.microsoft.com> 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
 | 
Pages: 1
Prev: email as pdf print area excel 2007
Next: sumif color cells with same month